SQL中JSON_ARRAY解嵌后如何对列执行COUNT、SUM等聚合运算

你可以通过**子查询/CTE（公共表表达式）**封装解嵌后的结果集，再在外层执行统计运算即可，同时可以优化原有解嵌逻辑减少重复代码。

方案1：直接改造现有SQL（改动最小） #

把你已写好的解嵌逻辑封装为CTE，后续所有统计都基于这个CTE操作即可。示例代码如下：

WITH 解嵌后数据集 AS (
    SELECT
           -- 注意把->替换为->>，直接返回文本格式方便后续计算
           json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'booking_date' AS 交易日期,
           -- 金额字段转数值类型，才能支持求和、求平均等运算
           (json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'amount')::numeric AS 交易金额,
           json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'currency' AS 币种,
           json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'counter_holder' AS 交易对手名称,
           json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'counter_iban' AS 交易对手IBAN,
           json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers')->>'category_id' AS 分类ID
    FROM loan_applications AS l
    LEFT JOIN risk_scores AS p1
      ON l.id = p1.loan_application_id
    WHERE human_readable_id = 'XXX'
)
-- 以下为统计逻辑示例：统计每个分类的交易笔数、总交易金额
SELECT 
    分类ID,
    COUNT(*) AS 分类交易笔数,
    SUM(交易金额) AS 分类总金额
FROM 解嵌后数据集
GROUP BY 分类ID
ORDER BY 分类交易笔数 DESC;

方案2：优化解嵌逻辑（性能更高、代码更简洁） #

将json_array_elements放到FROM子句通过横向连接单次解嵌数组，避免每个字段都重复写解嵌逻辑：

WITH 解嵌后数据集 AS (
    SELECT
           t.turnover->>'booking_date' AS 交易日期,
           (t.turnover->>'amount')::numeric AS 交易金额,
           t.turnover->>'currency' AS 币种,
           t.turnover->>'counter_holder' AS 交易对手名称,
           t.turnover->>'counter_iban' AS 交易对手IBAN,
           t.turnover->>'category_id' AS 分类ID
    FROM loan_applications AS l
    LEFT JOIN risk_scores AS p1
      ON l.id = p1.loan_application_id
    -- 单次解嵌turnovers数组，每个元素对应一行交易记录
    LEFT JOIN LATERAL json_array_elements(p1.json_data ->'accountSnapshot'->'turnovers') AS t(turnover) ON TRUE
    WHERE human_readable_id = 'XXX'
)
-- 统计逻辑可按需替换，示例如下：
-- 1. 统计所有交易总金额：
-- SELECT SUM(交易金额) AS 总交易金额 FROM 解嵌后数据集;
-- 2. 按日期统计每日交易笔数：
-- SELECT 交易日期, COUNT(*) AS 每日交易笔数 FROM 解嵌后数据集 GROUP BY 交易日期 ORDER BY 交易日期;
-- 3. 统计各币种交易总金额：
-- SELECT 币种, SUM(交易金额) AS 对应币种总金额 FROM 解嵌后数据集 GROUP BY 币种;

常见统计场景说明 #

你可以基于封装好的解嵌后数据集自定义统计规则，常用的聚合函数如下：

• 计数统计：用COUNT(*)统计行数、COUNT(DISTINCT 字段名)统计字段去重后的数量
• 数值运算：用SUM(字段名)求和、AVG(字段名)求平均值、MAX(字段名)/MIN(字段名)求最大/最小值
• 分组统计：搭配GROUP BY 字段名对指定维度做分组计算

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