Sample Header Ad - 728x90

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