Creating index on nested json which is string in postgres jsonb

Question

I want to query jsonb column with the nested field.

select * 
from TABLE_NAME 
where is_active=true 
and extra_info->'leaderboardConfig'->>'tier'= ??? 
order by id desc limit ?"

here extra_info has a lot of other information. while I am interested in leaderboardConfig.tier = "Bronze";

My extra info looks like this

{
    "info":"something",
     "inf2":"something",
    "leaderboardConfig": {
        "profile":123,
        "info1":"something",
        "info2":"something",
        "tier":"Bronze"

    }
}

how do i create index on extra_info->'leaderboardConfig'->>'tier'

how do i create index on extra_info->'leaderboardConfig'->>'tier' — vaibhav
– vaibhav, Commented Sep 26, 2022 at 13:00

user330315 · Accepted Answer · 2022-09-26 13:28:40Z

2

Just use the exact expression you use in your query when creating the index:

create index on the_table ( (extra_info->'leaderboardConfig'->>'tier') );

Note the extra pair of parentheses which is required because the index is only built on a single expression.

answered Sep 26, 2022 at 13:28

user330315

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

Collectives™ on Stack Overflow

Creating index on nested json which is string in postgres jsonb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related