Database Administration
postgresql postgresql-9.4 array json
Updated Fri, 20 May 2022 09:13:38 GMT

PostgreSQL JSON query array against multiple values


I want to write a query against jsonb type 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"

Solution

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

SELECT d
FROM   grp
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?


External Links

External links referenced by this document:

Linked Articles

Local articles referenced by this article:

References