Postgresql join on json array-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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