PostgreSQL如何统计jsonb数组同键值并输出汇总JSON对象

在PostgreSQL中可以通过内置JSONB函数组合实现该需求，实现逻辑如下：

• 第一步：使用jsonb_array_elements()将JSON数组展开，每一行对应数组中的一个JSON对象
• 第二步：使用jsonb_each_text()将每个JSON对象拆分为键、值两列
• 第三步：将值转为整数类型后按键分组求和
• 第四步：使用jsonb_object_agg()将分组结果重新聚合为汇总JSON对象

示例代码 #

基于你提供的测试数组直接运行的代码

SELECT jsonb_object_agg(key, sum_val) AS stat_result
FROM (
    SELECT 
        (key_value).key, 
        SUM((key_value).value::INTEGER) AS sum_val
    FROM (
        -- 拆解每个JSON对象为键值对
        SELECT jsonb_each_text(single_obj) AS key_value
        FROM jsonb_array_elements(
-- 此处可替换为你视图中对应的jsonb列，以下为示例测试数据
'[
  {"Android": 2,"Windows": 1,"Macintosh": 1},
  {"iOS": 1,"Android": 2,"Windows": 2,"Macintosh": 2},
  {},
  {"Android": 1,"Windows": 1},
  {"Android": 1},
  {"iOS": 1,"Android": 2},
  {"iOS": 2,"Android": 1},
  {"iOS": 2},
  {"Android": 1},
  {"iOS": 2,"Windows": 1},
  {"Android": 5},
  {},
  {},
  {"iOS": 1,"Android": 1},
  {},
  {},
  {"Windows": 3}
]'::JSONB
        ) AS arr_ele(single_obj)
    ) AS kv_split
    GROUP BY (key_value).key
) AS sum_result;

运行后返回结果完全匹配你需要的输出格式：

{
  "Android": 16,
  "Windows": 8,
  "Macintosh": 3,
  "iOS": 9
}

从视图中查询的通用写法

假设你的视图名为your_view，存储JSONB数组的列名为jsonb_arr_col，需要按业务主键id分组统计每个id对应的汇总结果，写法如下：

SELECT 
    id,
    jsonb_object_agg(key, sum_val) AS stat_result
FROM (
    SELECT 
        t.id,
        (kv).key,
        SUM((kv).value::INTEGER) AS sum_val
    FROM your_view t,
         jsonb_array_elements(t.jsonb_arr_col) AS arr(single_obj),
         jsonb_each_text(arr.single_obj) AS kv
    GROUP BY t.id, (kv).key
) AS res
GROUP BY id;

注意事项 #

• 空JSON对象{}在jsonb_each_text()处理时不会生成任何行，会自动跳过无需额外过滤
• 如果你的JSON值存在非整数的异常情况，可以加类型判断逻辑避免报错，示例：SUM(CASE WHEN jsonb_typeof(single_obj -> (kv).key) = 'number' THEN (kv).value::INTEGER ELSE 0 END)

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