Query JSON array of obejcts against multiple values
35
votes
1
answer
79555
views
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:
~~~pgsql
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 (https://dba.stackexchange.com/questions/89971/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"
Asked by BartZ
(453 rep)
Feb 28, 2016, 01:49 AM
Last activity: Aug 1, 2023, 02:00 PM
Last activity: Aug 1, 2023, 02:00 PM