PostgreSQL indexing JSONB array

Question

Here is my json

jsondata
------------------------------------
{"key1": 1, "keyset": [10, 20, 30]}
{"key1": 1, "keyset": [10, 20]}
{"key1": 1, "keyset": [30]}
{"key1": 1 }
{"key1": 1, "key2": 1}

I tried creating index keyset for above example
1st index using btree

CREATE INDEX test_keyset ON test_table (jsondata->'keyset');

2nd index using gin

CREATE INDEX test_keyset ON test_table USING GIN(jsondata->'keyset');

and query to select keyset value 10,

SELECT jsondata
FROM test
   JOIN LATERAL jsonb_array_elements_text(jsondata->'keyset') a(v)
      ON TRUE
WHERE a.v::integer = 10;

but it is doing sequential scan(checking all rows), can anyone suggest me about which indexing method is correct(btree or gin) and efficient way of getting data from json using indexing for above example, I'm new to postgres

Maybe this can help: http://stackoverflow.com/a/18405706/3270427 — McNets, Apr 15 '17 at 18:30

score 4 · Accepted Answer · answered Apr 15 '17 at 18:47

Use gin index on the expression jsondata->'keyset':

create index test_keyset on test using gin((jsondata->'keyset'));

You should use the expression in your queries in conjunction with the @> operator:

select jsondata
from test
where jsondata->'keyset' @> '10'

              jsondata               
-------------------------------------
 {"key1": 1, "keyset": [10, 20, 30]}
 {"key1": 1, "keyset": [10, 20]}
(2 rows)

Test if the planner can use the index:

set enable_seqscan to off;

explain analyse
select jsondata
from test
where jsondata->'keyset' @> '10'

                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=8.00..12.02 rows=1 width=55) (actual time=0.024..0.025 rows=2 loops=1)
   Recheck Cond: ((jsondata -> 'keyset'::text) @> '10'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on test_keyset  (cost=0.00..8.00 rows=1 width=0) (actual time=0.014..0.014 rows=2 loops=1)
         Index Cond: ((jsondata -> 'keyset'::text) @> '10'::jsonb)
 Planning time: 0.576 ms
 Execution time: 0.066 ms
(7 rows)

PostgreSQL indexing JSONB array

1 Answers1