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!

Community · Accepted Answer · 2017-05-23 12:15:14Z

4

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 10, 2015 at 20:50

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

3 Comments

Nischal Kumar Over a year ago

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?

Ulvi Over a year ago

@NischalKumar did you find any answer?

Nischal Kumar Over a year ago

@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.

Collectives™ on Stack Overflow

Index JSON Array in Postgres DB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related