'How to index a jsonb nexted column that uses Getpath operator #>> so we can a specfic value

We have a jsonb column 'roller' as

{
  "roller": {
    "dob": "8/16/1956",
    "ext": {
      "helio_status": ""
    },
    "grade": "11",
    "gender": "M",
    "sis_id": "3456704387",
    "location": {
      "zip": "99999"
    },
    "state_id": "546556560",
    "roller_number": "ASWE51460438"
  }
}

We pass a variable that we want to find in {roller, sis_id} to check if it exists

 u.roller#>>'{roller, sis_id}' = 'SE223dsd'

We created a gin index on roller but it does not work, it always does a seq scan on all of them.

Is there a specific type of index that will be better? Any assistance appreciated.

postgresqlindexingjsonbpostgresql-12


Solution 1:[1]

If you always look for that specific path, a regular B-Tree index is probably the best choice:

create index on the_table ( (roller#>>'{roller, sis_id}') );

You have to make sure that you always use the expression roller#>>'{roller, sis_id}' in your WHERE clause. Not something that is just equivalent like e.g. roller -> 'roller' ->> 'sis_id'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions