How to create jsonb index for top level keys in PostgreSQL?

Question

I have json data in my jsonb column json_struct with the following structure:

{
    "key1":{
            ...
            "value": "foo",
            ...
           },
    "key2":{
            ...
            "value": "bar",
            ...
           }
}

I want to create a index on the keys to speed up queries like:

SELECT * from my_data_table where json_struct -> 'key1' ->> 'value' like 'foo' AND json_struct -> 'key2' ->> 'value' like 'bar';

The problem is that I cannot create an index for each key because there are too many (>10.000) of them.

Any idea how to index my table to speed up my query ?

Answer 1

There is no single index that can do that; the only option is to create an index for each attribute you want to compare. If you wanted to compare with =, a GIN index would work, but not for pattern matching.

That is not a specific shortcoming of PostgreSQL's JSON implementation: even if your attributes were regular table columns, you would need two indexes if you want to perform an efficient LIKE search on two columns.