Using Postgres 9.5 GIN indexes and JSONB

Question

I'm trying to create a Postgres GIN index to speed up the following query:

CREATE TABLE foo (
    id serial primary key,
    a jsonb not null
);

insert into foo(a) values
    ('[{"b": "aaa"}, {"b": "ddd"}]'::jsonb),
    ('[{"b": "aaa"}, {"b": "aaa"}]'::jsonb),
    ('[{"b": "aaa"}]'::jsonb),
    ('[{"b": "aaa"}]'::jsonb),
    ('[{"b": "aaa"}]'::jsonb),
    ('[{"b": "bbb"}]'::jsonb),
    ('[{"b": "bbb"}]'::jsonb),
    ('[{"b": "bbb"}]'::jsonb),
    ('[{"b": "ccc"}]'::jsonb),
    ('[]'::jsonb);

select distinct id from (
    select id, jsonb_array_elements(a)->>'b' as b from foo
) t where t.b = 'aaa'

Is such a thing possible in Postgres? I am open to other alternatives as well. Unfortunately, I can't normalize the table, so I'll need to work with the table structure that I already have.

Patrick · Accepted Answer · 2016-01-23 07:39:44Z

1

Yes, you can apply a GIN index here but it may not be particularly useful:

CREATE INDEX find_fast_jsonb_value
ON foo USING GIN (a jsonb_path_ops);

Now you still have to search through the arrays for matching key/value pairs. Your query then becomes:

SELECT DISTINCT id
FROM foo, jsonb_array_elements(a) AS t(b)  -- Implicit LATERAL join
WHERE b @> '{"b": "aaa"}';                 -- Comparing json key/values here

This also places the set-returning-function jsonb_array_elements() in the FROM clause, where it belongs.

edited Jan 23, 2016 at 7:39

answered Jan 23, 2016 at 6:51

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RandomBK Over a year ago

Hi Patrick, thanks for the reply. However, I'm receiving two errors from this method: ERROR: index expression cannot return a set and ERROR: syntax error at or near "->>" LINE 27: FROM foo, jsonb_array_elements(a)->>'b' AS t(b) -- Implicit ... I wonder, what would be the performance difference of using a btree index, compared to maintaining a manual index using triggers? That might be easier to maintain and extend in the future. Thanks!

RandomBK Over a year ago

Thanks for your time. I ended up finding the answer in another thread: stackoverflow.com/questions/26499266/…

RandomBK Over a year ago

The query I ended up using was SELECT DISTINCT id from foo where a @> '[{"b": "aaa"}]', which was a bit more efficient

minusf Over a year ago

just a note about GIN indexes: there are 2 types, and jsonb_path_ops from this answer is the non-default one. Both index types have their pros and cons, but jsonb_path_ops can basically only be used with the containment operator (@>).

Collectives™ on Stack Overflow

Using Postgres 9.5 GIN indexes and JSONB

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related