Postgresql join on json array

Question

I'm trying to write a query that involves a join on JSON array. Following are the tables

PROPOSAL Table:

id       details
1        {units: [1, 2]}
2        {units: [1]}

UNITS Table:

id   DMA_ID
1    1
2    2

DMA Table:

id    name
1     SOLAPUR
2     PUNE

Output:

DMA_NAME,   Count of proposals
SOLAPUR     2
PUNE        1

Few of my attempts are,

SELECT 
    dma.name,
    count(
    CASE
        WHEN EXISTS p.id from proposals_proposal p where (p.details->>'units')::jsonb->>u.unit_id is not null then 1
        ELSE 0
    )
FROM signs_dma dma JOIN signs_unit u ON dma.id=u.dma_id;

Any help is appreciated. Thanks in advance!

Well for one thing the key inside the json is case-sensitive. If you're storing it as UNITS and looking-up units that's not going to work. — coladict
– coladict, Commented Aug 29, 2022 at 13:25

Slava Rozhnev · Accepted Answer · 2022-08-29 13:49:50Z

2

You can use next query:

SELECT DMA.name, COUNT(*) PROPOSAL_COUNT
FROM DMA 
JOIN UNITS ON DMA.id=UNITS.dma_id
JOIN PROPOSAL ON to_jsonb(UNITS.id) <@ (PROPOSAL.details->'UNITS')
GROUP BY DMA.name;

SQL editor online

edited Aug 29, 2022 at 13:49

answered Aug 29, 2022 at 13:44

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

Comments

Premkumar chalmeti · Accepted Answer · 2022-08-30 10:49:20Z

1

This did the job

with base_proposal as (select id, jsonb_array_elements(details->'units') unit_id, created from proposals_proposal)
    select
        TRIM(dma.name) DMA, count(distinct bp.id) Proposals
    from base_proposal bp 
    join signs_unit unit on unit.unit_id=bp.unit_id
    join signs_dma dma on unit.dma_id = dma.id 
    where bp.created between {{start_date}} and {{end_date}} 
    group by TRIM(dma.name) order by count(distinct bp.id);

answered Aug 30, 2022 at 10:49

Premkumar chalmeti

1,0381 gold badge11 silver badges25 bronze badges

Collectives™ on Stack Overflow

Postgresql join on json array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related