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

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?

CodePudding user response：

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);