I am trying to use the jsonb_path_exists function in PostgreSQL to check whether a JSONB field (with list of objects) contains an object where a specific key has a value that belongs to a given list of values.

A simple condition like this works fine (comparing with single value):

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id == "0e66fef9-0f59-4742-8669-74f41fc26a3f")');

However, I want to check whether the id field is one of several possible values instead of just a single one. I attempted the following query using the "IN" operator:

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ("0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"))');

and

WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ["0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"])');

But this results in a syntax error:

ERROR: syntax error in jsonpath near " " SQL state: 42601

I would like to know if PostgreSQL supports checking for membership in a list using "IN" within JSONPath, or if there is an alternative way to achieve this.

I am trying to use the jsonb_path_exists function in PostgreSQL to check whether a JSONB field (with list of objects) contains an object where a specific key has a value that belongs to a given list of values.

A simple condition like this works fine (comparing with single value):

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id == "0e66fef9-0f59-4742-8669-74f41fc26a3f")');

However, I want to check whether the id field is one of several possible values instead of just a single one. I attempted the following query using the "IN" operator:

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ("0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"))');

and

WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ["0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"])');

But this results in a syntax error:

ERROR: syntax error in jsonpath near " " SQL state: 42601

I would like to know if PostgreSQL supports checking for membership in a list using "IN" within JSONPath, or if there is an alternative way to achieve this.

• postgresql
• jsonb

1 Answer 1

Unfortunately, there's no in() or =any() in JSONPath. You need to string together a list of alternative comparisons separated by ||:

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field
, '$[*] ? (   @.id == "0e66fef9-0f59-4741-9669-74f41fc26a3f"
           || @.id == "1e66fef9-0f59-4741-9669-74f41fc26a3f"))');

Or extract the value and use SQL in() or =any():

SELECT t.id
FROM some_scheme.some_table as t
WHERE exists(
 select from jsonb_array_elements(t.jsonb_field)_(e)
 where e->>'id'=any('{ 1e66fef9-0f59-4741-9669-74f41fc26a3f
                      ,0e66fef9-0f59-4741-9669-74f41fc26a3f}'::text[]));

Or use it directly with a containment operator @>:

SELECT t.id
FROM some_scheme.some_table as t
WHERE t.jsonb_field 
 @> any(array[ '{"id":"1e66fef9-0f59-4741-9669-74f41fc26a3f"}'::jsonb
              ,'{"id":"0e66fef9-0f59-4741-9669-74f41fc26a3f"}'::jsonb]);

In the last example, it doesn't matter if the objects in the array in jsonb_field have other keys, it's considered a match if among them, they have an id key with that value.