Postgres 9.4+ jsonb: Index dictionaries nested in arrays

Question

Given a table

create table tmp (data jsonb not null);

and data

insert into tmp (data) values ('{"root": [{"name": "item1"}, {"name": "item2"}]}');

I need to index jsonb column 'data' to quickly answer queries like

select * from tmp where data->'root' @> '[{"name": "item1"}]';

Is it possible in Postgres 9.4+?

@Hackerman you were close: a correct solution appears to be data->'root'. — user2740947

user2740947 user2740947 · Accepted Answer · 2017-09-01T20:45:25

After a bit of debugging, I learned that indices created for outer json elements (e.g. "root") also apply to all the nested elements in the hierarchy. So the correct solution in my case is:

CREATE INDEX idx_tmp_data_root ON tmp USING gin ((data->'root') jsonb_path_ops);

I opted for jsonb_path_ops index operator class since it supports containment queries @> as required and results in a more compact and faster index as opposed to a default index type.

And here is a full demonstration:

First, create a table and load data:

-> SET enable_seqscan = OFF; -- force postgres to use indices if any
-> create temporary table tmp (data jsonb not null);
-> insert into tmp (data) values ('{"root": [{"name": "item1"}, {"name": "item2"}]}');

Query without an index:

-> explain select * from tmp where data->'root' @> '[{"name": "item1"}]';

QUERY PLAN
Seq Scan on tmp  (cost=10000000000.00..10000000029.65 rows=1 width=32)
   Filter: ((data -> 'root'::text) @> '[{"name": "item1"}]'::jsonb)
(2 rows)

Query with an index:

-> CREATE INDEX idx_tmp_data_root ON tmp USING gin ((data->'root') jsonb_path_ops);
-> explain select * from tmp where data->'root' @> '[{"name": "item1"}]';

QUERY PLAN
Bitmap Heap Scan on tmp  (cost=8.00..12.02 rows=1 width=32)
  Recheck Cond: ((data -> 'root'::text) @> '[{"name": "item1"}]'::jsonb)
  ->  Bitmap Index Scan on idx_tmp_data_root  (cost=0.00..8.00 rows=1 width=0)
    Index Cond: ((data -> 'root'::text) @> '[{"name": "item1"}]'::jsonb)
(4 rows)

-> SET enable_seqscan = ON;

Postgres 9.4+ jsonb: Index dictionaries nested in arrays

1 Answers