Index JSON Array in Postgres DB

Question

I have a table where each row has a JSON structure as follows that I'm trying to index in a postgresql database and was wondering what the best way to do it is:

{
    "name" : "Mr. Jones",
    "wish_list": [
        {"present_name": "Counting Crows",
        "present_link": "www.amazon.com"},
        { "present_name": "Justin Bieber",
        "present_link": "www.amazon.com"},
    ]
}

I'd like to put an index on each present_name within the wish_list array. The goal here is that I'd like to be able to find each row where the person wants a particular gift through an index.

I've been reading on how to create an index on a JSON which makes sense. The problem I'm having is creating an index on each element of an array within a JSON object.

The best guess I have is using something like the json_array_elements function and creating an index on each item returned through that.

Thanks for a push in the right direction!

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

Please check JSONB Indexing section in Postgres documentation. For your case index config may be the following:

CREATE INDEX idx_gin_wishlist ON your_table USING gin ((jsonb_column -> 'wish_list'));

It will store copies of every key and value inside wish_list, but you should be careful with a query which hits the index. You should use @> operator:

SELECT jsonb_column->'wish_list'
FROM your_table WHERE jsonb_column->'wish_list' @> '[{"present_link": "www.amazon.com", "present_name": "Counting Crows"}]';

Strongly suggested to check existing nswers:

edited May 23 '17 at 12:15

Community

1
1

answered Dec 10 '15 at 20:50

Dmitry S

4,990
2
24
32

1

if the present link could be anything and the value is not pre-known, i.e, only present_name is known what should the query look like? – Nischal Kumar Mar 12 '21 at 10:48
@NischalKumar did you find any answer? – Ulvi Aug 20 '21 at 19:02
@Ulvi `SELECT jsonb_column->'wish_list' FROM your_table WHERE wish_list @> '[{"present_name":"Counting Crows"}]' `. In here we can declare wish_list as jsonb column type. I did not look into indexing for this case. Please share your thoughts, how we can index for the same use case. – Nischal Kumar Sep 08 '21 at 18:25

Index JSON Array in Postgres DB

1 Answers1