34

I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.

Given this example table:

CREATE TABLE grp(d jsonb NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

I found similar question (PostgreSql JSONB SELECT against multiple values) and managed to achieve what I want on simple array using this query:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

However, I can't make it work when when array contains JSON objects:

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Here is what I expect from my query:

grp "First" -> customer "1"

grp "Third" -> customer "5"

1 Answer 1

36

Postgres 12 or later

You can use an SQL/JSON path expression with the @? operator:

SELECT d
FROM   grp
WHERE  d @? '$.customers[*].id ? (@ == "1" || @ == "5")';

Note the double quotes: your id values are strings, not numbers.

Or more compact for long lists of values with like_regex in the filter expression:

...
WHERE  d @? '$.customers[*].id ? (@ like_regex "^1|5$")'

See:

Any version since Postgres 9.3

There is a way: combine the containment operator @> with the ANY construct:

...
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.

You can use an index for this:

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.