CODEWITHSUNDEEP
sqlpostgresqlindexingjsonb
sumanth
sumanth

Reputation: 113

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

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

Upvotes: 1

Views: 586

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246868

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)

Upvotes: 1

Related Questions