PostgreSQL查询JSON对象数组列 按条件提取数值并求和问题

问题原因 #

你遇到的报错核心是jsonb_array_elements属于集合返回函数，无法直接嵌套在sum()这类聚合函数的参数中使用，需要先完成JSON数组的行展开操作，再对展开后的结果做聚合计算。

最优实现写法（推荐，性能更高） #

使用LATERAL横向连接仅对每个JSON数组做1次展开，避免多次调用解析函数：

SELECT
    SUM(
        CASE 
            WHEN elem->>'a' = 'bla' AND elem->>'c' = 'foo' 
            THEN regexp_replace(elem->>'b', '\D','','g')::numeric 
            ELSE 0 
        END
    ) AS total_sum
FROM x.y,
LATERAL jsonb_array_elements("jsons") AS elem
WHERE "some filter here"
  AND "condition 1"
  AND "condition 2"
  AND "condition 3";

可选写法（可读性更强，适合新手理解） #

先用CTE把JSON数组展开为单行结构，提取所有需要的字段后再做聚合：

WITH expanded_json_data AS (
    SELECT
        jsonb_array_elements("jsons") ->> 'a' AS a,
        jsonb_array_elements("jsons") ->> 'c' AS c,
        regexp_replace(jsonb_array_elements("jsons")->> 'b', '\D','','g')::numeric AS num
    FROM x.y
    WHERE "some filter here"
      AND "condition 1"
      AND "condition 2"
      AND "condition 3" 
)
SELECT
    SUM(CASE WHEN a = 'bla' AND c = 'foo' THEN num ELSE 0 END) AS total_sum
FROM expanded_json_data;

优化提示 #

如果存在b字段为空、格式异常的场景，可以给数值转换逻辑加COALESCE兼容异常，避免查询报错：

COALESCE(regexp_replace(elem->>'b', '\D','','g')::numeric, 0)

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