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

Revisions

  1. plcgi1 revised this gist Feb 27, 2020. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions psql - jsonb - search in array of object
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,8 @@ 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"}]')
  2. plcgi1 revised this gist Feb 27, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions psql - jsonb - search in array of object
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,6 @@
    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'));

    insert into tbl(usr) VALUES('[{ "_id" : "2", "count" : "4" }, { "_id" : "3", "count": "3"}]')
  3. plcgi1 revised this gist Feb 27, 2020. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions psql - jsonb - search in array of object
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,7 @@
    CREATE TABLE tbl (tbl_id int, usr jsonb);

    CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));

    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"}]')

  4. plcgi1 created this gist Feb 27, 2020.
    9 changes: 9 additions & 0 deletions psql - jsonb - search in array of object
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,9 @@
    CREATE TABLE tbl (tbl_id int, usr jsonb);

    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"}]';