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}}'

user330315 · Accepted Answer · 2018-03-19 07:56:50Z

4

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);

answered Mar 19, 2018 at 7:56

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

David McEleney Over a year ago

as I only want to index by one or two fields, is it possible to isolate the index to particular field?? e.g. CREATE INDEX on the_table USING GIN ((customer -> 'Customer' -> 'ProfileId') jsonb_path_ops);

user330315 Over a year ago

For that expression you don't need a GIN index. A btree index will be much more efficient. The GIN index is intended for situations where you don't know which keys (or paths) in the JSON your condition will contain

radtek Over a year ago

Something like this would work no? CREATE INDEX idx_content_metadata_pn_qwire_id ON your_table((customer->>'Customer'->> 'ProfileId'));

Collectives™ on Stack Overflow

Postgres jsonb index on nested integer field

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related