Postgres JSONB数组对象：统计每行delicious与not-delicious数量

针对你这个tasks表中JSONB数组的统计需求，我可以给你一个简洁的SQL方案，完美匹配你想要的输出格式：

SELECT
    t.id,
    COALESCE(SUM(CASE WHEN v.value = 'delicious' THEN 1 ELSE 0 END), 0) AS delicious,
    COALESCE(SUM(CASE WHEN v.value = 'not-delicious' THEN 1 ELSE 0 END), 0) AS not_delicious
FROM
    tasks t
LEFT JOIN LATERAL jsonb_array_elements(t.reviews) AS arr(elem) ON true
LEFT JOIN LATERAL jsonb_each_text(arr.elem) AS v(key, value) ON true
GROUP BY
    t.id
ORDER BY
    t.id;

代码细节解释： #

• jsonb_array_elements(t.reviews)：把每行的reviews JSONB数组拆分成单独的JSON对象行，让每个数组元素都变成一条独立的记录，方便后续处理。
• jsonb_each_text(arr.elem)：把每个仅含单个键值对的JSON对象拆分成key和value文本列，这样我们就能直接判断值的类型。
• CASE语句配合SUM：分别统计delicious和not-delicious的出现次数，用COALESCE把可能出现的NULL（比如某类值完全没有的情况）转换成0，确保输出格式统一。
• 最后按id分组，就能得到每行对应的统计结果。

用你提供的测试数据运行这个SQL，会得到完全符合预期的输出：

id | delicious | not_delicious
----+-----------+---------------
  1 |         1 |             1
  2 |         0 |             2
  3 |         2 |             1

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