Is GIN index on postgres jsonb column nested?

Question

I have a json which I am storing it as jsonb in postgres.

{
  "name": "Mr. Json",
  "dept":{
    "team":{
      "aliases":["a1","a2","a3"],
      "team_name": "xyz"
    },
    "type":"engineering",
    "lead":"Mr. L"
  },
  "hobbies": ["Badminton", "Chess"],
  "is_active": true
}

Have created a GIN index on the column

I need to do exact match queries like all rows containing type='engineering' and lead='Mr. L'. I am currently doing containment queries like:

data @> '{"dept":{"type":"engineering"}}' and data @> '{"dept":{"lead":"Mr. L"}}'

I saw the query plan which shows GIN index is being used but I am unsure if this works or if there is some better way of achieving this.

Will I have to construct another index on nested keys?

Does indexing a jsonb column indexes the nested keys or just the top level ones?

Also please share some good resource on this.

Why not just data @> '{"dept":{"lead":"Mr. L","type":"engineering"}}'? Your query will use the index twice, while this (which is equivalent) will use it only once. — pozs

Łukasz Kamiński Łukasz Kamiński · Accepted Answer · 2017-06-02T10:36:44

From docs:

The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.

For containment @> it works with nested values. For other operators it works only for top-level keys or whatever level is used in expression index. Also, according to documentation, using expression index on level you want to query will be faster than simple index on whole column (makes sense as size is smaller).

If you are doing only containment search, consider using jsonb_path_ops while building your index. It is smaller and faster.

Is GIN index on postgres jsonb column nested?

1 Answers