Creating index for a Json attribute stored in a JSONb column which contains array of json in Postgres

Question

I am using postgres from past few days and i came up with requirement which i am trying to find solution.

I am using postgres sql.
I have a Table which is like this

CREATE TABLE location (id location_Id, details jsonb);

INSERT INTO location (id,details)
VALUES (1,'[{"Slno" : 1, "value" : "Bangalore"},
             {"Slno" : 2, "value" : "Delhi"}]');

INSERT INTO location (id,details)
VALUES (2,'[{"Slno" : 5, "value" : "Chennai"}]');

From the above queries you can see that a jsonb column with name details is present which has an array of json as value stored. The data is stored like this because of some requirement.

Here i want to create an index for the Slno property present in jsonb
column values.

Can someone help me out in finding solution for this as it would be very helpful.

Thanks

Laurenz Albe · Accepted Answer · 2016-07-13 08:52:12Z

1

It is not an index for a single JSON property, rather for the whole attribute, but you can use it to perform a search like you want:

CREATE INDEX locind ON location USING GIN (details jsonb_path_ops);

If you need the index for other operations except @> (contains), omit the jsonb_path_ops. This will make the index larger and slower.

Now you can search for the property using the index:

EXPLAIN (VERBOSE) SELECT id FROM location WHERE details @> '[{"Slno" : 1}]';

                               QUERY PLAN
-------------------------------------------------------------------------
 Bitmap Heap Scan on laurenz.location  (cost=8.00..12.01 rows=1 width=4)
   Output: id
   Recheck Cond: (location.details @> '[{"Slno": 1}]'::jsonb)
   ->  Bitmap Index Scan on locind  (cost=0.00..8.00 rows=1 width=0)
         Index Cond: (location.details @> '[{"Slno": 1}]'::jsonb)
(5 rows)

answered Jul 13, 2016 at 8:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

sumanth Over a year ago

Thank you for posting. My doubt is can't an index be created for SlNo property alone.

sumanth Over a year ago

I mean creating index for Slno attribute.

Laurenz Albe Over a year ago

No, but what is lacking in the index I described?

sumanth Over a year ago

I have a specific requirement to create index in the way i have mentioned above that is why i was asking.

Collectives™ on Stack Overflow

Creating index for a Json attribute stored in a JSONb column which contains array of json in Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related