Skip to content

Instantly share code, notes, and snippets.

@plcgi1

plcgi1/psql - jsonb - search in array of object

Last active February 27, 2020 11:36
Show Gist options
  • Save plcgi1/5dc2e1f54864712929d594cbb1c1504c to your computer and use it in GitHub Desktop.
Save plcgi1/5dc2e1f54864712929d594cbb1c1504c to your computer and use it in GitHub Desktop.
Download ZIP
psql - jsonb - search in array of object
Raw
psql - jsonb - search in array of object
CREATE TABLE tbl (tbl_id int, usr jsonb);
-- for pg10
CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));
-- for pb >9.4
CREATE INDEX tbl_special_idx ON tbl USING gin (usr jsonb_path_ops);
insert into tbl(usr) VALUES('[{ "_id" : "2", "count" : "4" }, { "_id" : "3", "count": "3"}]')
insert into tbl(usr) VALUES('[{ "_id" : "2", "count" : "5" }, { "_id" : "3", "count": "36"}]')
SELECT obj.val->>'_id' AS _id, obj.val->>'count' AS count
FROM tbl t
JOIN LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '3'
WHERE t.usr @> '[{"_id":"3"}]';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment