Can I index and search nested object keys in JSON on postgres?

Question

If I have a table called configurations where rows contain a jsonb column called data with values similar to the following:

{
    "US": {
        "1234": {
            "id": "ABCD"
        }
    },
    "CA": {
        "5678": {
            "id": "WXYZ"
        }
    }
}

My hope is to be able to write a query akin to the following:

select * from configurations where data->'$.*.*.id' = 'WXYZ'

(Please note: I'm aware that the SQL above is not correct, treat it as pseudo.)

Questions:

What is the correct syntax to perform the query I've written above?
What type of index would I need to create to ensure I'm not scanning the entire table using any query from my previous question?

Because the values in this column will vary in structure and represent information from external systems. — Alexander Trauzzi
– Alexander Trauzzi, Commented Sep 27, 2021 at 16:28

jjanes · Accepted Answer · 2021-09-27 16:58:29Z

1

You can turn your pseudo code into real jsonpath code:

select * from configurations where data @@ '$.*.*.id == "WXYZ"'

And this can use a default gin index on "data":

create index on configurations using gin (data);

edited Sep 27, 2021 at 16:58

answered Sep 27, 2021 at 16:45

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

6 Comments

Alexander Trauzzi Over a year ago

Neat, I'll give that a shot! Is there any specific syntax I should use to create that gin index?

Alexander Trauzzi Over a year ago

Thanks for the update! Do you happen to know if the gin index can be combined with other non-json columns?

Alexander Trauzzi Over a year ago

Hmm, my explain for the query select * from configurations where data @@ '$.*.*.id' is not null; doesn't appear to be using the gin index I create. Still shows "Seq scan".

jjanes Over a year ago

If the table is small, or if it thinks too many rows will match, it won't find the index enticing to use. Is either of those the case here? In my hands, not only was it used, but it was much more efficient than i thought it would be.

jjanes Over a year ago

It can combine with other indexes by the use of bitmap scans.

|

Collectives™ on Stack Overflow

Can I index and search nested object keys in JSON on postgres?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related