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"

Erwin Brandstetter · Accepted Answer · 2025-12-05 23:13:22Z

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$")'

Either formulation allows a bitmap index scan on a GIN index, with default or with jsonb_path_ops operator class, like:

CREATE INDEX grp_d_gin_path_ops_idx ON grp USING gin (d jsonb_path_ops);

More variants in this fiddle.

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. But 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?

You mentioned the ANY/jsonb approach can be indexed, thanks for that! The path expression method can't really be indexed, can it? The older ANY seems far superior for this reason. I'm worried people will jump for the "new" way without realizing this. Am I missing anything? — moodboom
– moodboom, Commented Dec 4 at 17:13
@moodboom: The path expression can use an index too. I upgraded and added a fiddle above. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 5 at 23:15

Stack Exchange Network

Query JSON array of obejcts against multiple values

1 Answer 1

Postgres 12 or later

Any version since Postgres 9.3

Your Answer

Linked

Hot Network Questions

Query JSON array of obejcts against multiple values

1 Answer 1

Postgres 12 or later

Any version since Postgres 9.3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions