Query JSON array of obejcts against multiple values

Question

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"

Answer 1

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:

• Find rows containing a key in a JSONB array of records

• Pattern-matching for JSON values

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:

• Index for finding an element in a JSON array

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?