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.

Is GIN index on postgres jsonb column nested?

1 Answers