CODEWITHSUNDEEP
jsonpostgresqlpathpsqljsonb
Sam Sam
Sam Sam

Reputation: 27

postgres find whether json path exists

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:

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

Upvotes: 1

Views: 2335

Answers (1)

cur4so
cur4so

Reputation: 1820

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

Upvotes: 2

Related Questions