Query jsonb array objects in jsonb array objects (and index)

Question

select
        picks ->> 'pid' as playerid, e.id,
        e.sportid, e.leagueid,
        e.islive, e.status,
        e.locationid, e.participants, em.bets
From
    sfbr.eventmarket em
        join sfbr.events e on em.eventid = e.id,
    jsonb_array_elements(em.bets) b,
    jsonb_array_elements((b->> 'b')::jsonb) picks
where (picks ->> 'pid') is not null
  and (picks ->> 'ec') is null
  and e.startdatetime > '2019-09-04 07:29:00.690834' and (picks ->> 'pid') in
                        ('1880686','1796512','1838464','851658','66675')
order by e.startdatetime desc;

Hi, This query takes forever (2-3minutes), how can i write it so i dont have to do jsonb_array_elements twice!? is there any way to index 'pid' field in array inside of an array?

Thanks in advance

For Indexing Part you can try this https://stackoverflow.com/questions/36075918/postgresql-index-on-json — Akhilesh Mishra, Sep 16 '20 at 17:54

score 0 · Answer 1 · answered Sep 16 '20 at 21:51

GIN indexes can be used to search for keys or key/value pairs from jsonb columns for top level keys.

I tried to re-write your query to make it more readable. Without any sample data, it is hard to verify, but how does it perform compared to original? (I changed jsonb_array_elements((b->> 'b')::jsonb) to jsonb_array_elements(bets -> 'b'), which should already be json without casting.)

with m as (
    select 
        eventid,
        bets,
        jsonb_array_elements(bets -> 'b') as picks
    from 
        sfbr.eventmarket
), em as (
    select
        eventid,
        bets,
        picks ->> 'pid' as pid, 
        picks ->> 'ec' as ec
    from 
        m
), e as (
    select 
        id,
        sportid,
        leagueid,
        islive,
        status,
        locationid,
        participants,
        startdatetime
    from 
        sfbr.events
)
select
    em.pid,
    e.*,
    em.bets 
from 
    em 
inner join 
    e on em.eventid = e.id
where
    em.pid is not null and 
    em.ec is null and 
    e.startdatetime > '2019-09-04 07:29:00.690834' and 
    em.pid in ('1880686','1796512','1838464','851658','66675')
order by 
    e.startdatetime desc;

Query jsonb array objects in jsonb array objects (and index)

1 Answers1