1

I want to find whether a specific path exists in a jsonb value using a psql query.

For example, for this path: {"333":"opc":["1333"]}

This value should return true:

'{"333":{"opc":[{"1333":"3787"}]}}'

But these values should return false:

  • '{"333":{"opc":[{"104":"3787"}]}}'
  • '{"54":{"opc":[{"1333":"3787"},{"1334":"37"}]}}'
  • '{"333":{"opc":[]}}'

I've tried some variations using the @> operator but couldn't quite get the right syntax.

ex:

select 
  '{"333":{"opc":[{"1333":"3787"},{"1334":"37"}]}}'::jsonb @>   
  '{"333":{"opc":[{"1333"}]}}'::jsonb

this gives me an invalid syntax error

Brad
  • 159,648
  • 54
  • 349
  • 530
Sam Sam
  • 27
  • 3

1 Answers1

2

how about

select 
  case when
    (select e->'1333' from json_array_elements(data->'333'->'opc') e) is not null
    then true
    else false
  end as status
from t 
;

? http://sqlfiddle.com/#!15/2c794/17

cur4so
  • 1,750
  • 4
  • 20
  • 28
  • thank you for taking the time to reply. when I try your suggestion I get the following error... ERROR: more than one row returned by a subquery used as an expression. Let me give it a bit more testing and I will post again. – Sam Sam May 11 '16 at 16:08
  • Thanks for your reply. I ended up using this: select * from (select jsonb_array_elements(data->'333'->'opc')->'1333' as value from t ) e where e.value is not null – Sam Sam May 11 '16 at 16:58