PostgreSQL基于JSON数组关联查询的需求与问题求助

针对你的需求，这里提供两种可行的SQL写法，都能得到期望的统计结果：

解法一：展开JSON数组后关联统计 #

这种方式先将提案中的units数组拆分为单独行，再关联到单位和DMA表，最后去重统计提案数：

SELECT
    dma.name AS DMA_NAME,
    COUNT(DISTINCT p.id) AS "Count of proposals"
FROM
    PROPOSAL p
JOIN LATERAL jsonb_array_elements_text(p.details->'units') AS unit(id)
    ON TRUE
JOIN UNITS u
    ON u.id = unit.id::integer
JOIN DMA dma
    ON dma.id = u.DMA_ID
GROUP BY
    dma.name
ORDER BY
    dma.name;

解法二：用JSON包含判断关联统计 #

如果不想展开数组，可以直接判断单位ID是否存在于提案的units数组中，同样需要去重避免重复计数：

SELECT
    dma.name AS DMA_NAME,
    COUNT(DISTINCT p.id) AS "Count of proposals"
FROM
    DMA dma
JOIN UNITS u
    ON dma.id = u.DMA_ID
LEFT JOIN PROPOSAL p
    ON jsonb_contains(p.details->'units', to_jsonb(u.id))
GROUP BY
    dma.name
ORDER BY
    dma.name;

你的原SQL存在的问题 #

1. EXISTS语法错误：正确的EXISTS子句需要包裹完整的查询，比如EXISTS(SELECT 1 FROM proposals_proposal p WHERE ...)，而不是EXISTS p.id from ...的写法。
2. 数组包含判断逻辑错误：(p.details->>'units')::jsonb->>u.unit_id是取数组指定索引的元素，而非判断数组是否包含该单位ID，完全不符合需求。
3. COUNT函数用法错误：COUNT(CASE ... WHEN ... THEN 1 ELSE 0 END)会把0也计入统计，应该改用SUM(CASE ... WHEN ... THEN 1 ELSE 0 END)，或者直接用COUNT(DISTINCT)来统计唯一提案数。

内容的提问来源于stack exchange，提问作者Premkumar chalmeti