Indexing JSONB keys/values which are in different unique keys-CodePudding

in postgres, I have a below jsonb column which has around 5k keys along with values.

I want to filter these keys along with their values where priority/ age is greater than some specific value and also want to order them by the same.

{
  "100": {
    "name": "test",
    "priority": 5,
    "age": 10
  },
  "101": {
    "name": "test2",
    "priority": 10,
    "age": 20
  },
  "102": {
    "name": "test3",
    "priority": 15,
    "age": 25
  }                     
}

I am querying this using jsonb_path_query as below.

jsonb_path_query(match_data_3.json, '$.keyvalue() ? (@.value.priority >1)'

As these queries as taking more time, I want to add index for age / priority to make the filtering fast.

is it possible to achieve this ? if yes, how can I ? any help is much appreciated.

Most of the questions answered here have the indexes based on specific keys. like data->> 'priority'. But my root keys are different. couldn't find a suitable answer for my question.

CodePudding user response：

jsonb_path_query() won't use an index, but you can add a WHERE condition using the @? operator to filter out rows that match your JSON path condition:

select x.*
from the_table
  cross join jsonb_path_query(the_column, '$.** ? (@.priority > 1 && @.age > 10)') as x
where the_column @? '$.** ? (@.priority > 1 && @.age > 10)'

A GIN index on the_column can potentially be used by the @? operator. You will need to test if that really improves the query speed.

The best solution however, would be to use a properly normalized data model which can be indexed and queried efficiently.