Postgres jsonb index on nested integer field

Question

I've got the following data structure in my postgres database - a jsonb column called customer

{
    "RequestId": "00000000-0000-0000-0000-000000000000",
    "Customer": {
        "Status": "A",
        "AccountId": 14603582,
        "ProfileId": 172,
        "ReferralTypeId": 15
    }
    "Contact": {
        "Telephone": "",
        "Email": ""
    }   
}

I want to create an index on the ProfileId field, which is an integer.

I've been unable to find an example of how to create an index on a nested field.

The query I'm executing (which takes ~300s) is:

select id, customer from where customer @> '{"Customer":{"ProfileId": 172}}'

Answer 1

The operator classes jsonb_path_ops and jsonb_ops for GIN indexes support the @> operator.

So your query should be able to use the following index

create index on the_table using gin (customer);

which uses the default jsonb_ops operator.

According to the manual the jsonb_path_ops operator is faster but only supports the @> operator. So if that is the only type of condition you have (for that column), using jsonb_path_ops might be more efficient:

create index on the_table using gin (customer jsonb_path_ops);