Postgres按城市分组聚合jsonb列获取指定输出的方案咨询

你直接用jsonb_agg达不到效果是因为这个函数只会把多个jsonb元素合并成数组，不会处理数组内部字段的聚合计算，你的需求需要先把jsonb数组拆成标准行格式做聚合，再重新组装成数组，具体实现SQL如下：

WITH 
-- 处理JColA：按城市+ID分组求和amount
agg_col_a AS (
    SELECT
        city,
        id,
        MAX(name) AS name, -- 同ID下其他字段值固定，直接取任意值即可
        MAX(type) AS type,
        SUM(amount) AS amount,
        MAX(full_name) AS full_name
    FROM 你的表名, -- 替换为你自己的表名
         jsonb_to_recordset(JColA) AS a(id text, name text, type text, amount numeric, full_name text)
    GROUP BY city, id
),
-- 处理JColB：按城市+key分组求和value
agg_col_b AS (
    SELECT
        city,
        key,
        SUM(value::numeric)::text AS value -- value转数值求和后转回字符串，和你期望的输出格式保持一致
    FROM 你的表名,
         jsonb_to_recordset(JColB) AS b(key text, value text)
    GROUP BY city, key
)
-- 组装最终结果
SELECT
    a.city AS "City",
    jsonb_agg(jsonb_build_object(
        'id', a.id,
        'name', a.name,
        'type', a.type,
        'amount', a.amount,
        'full_name', a.full_name
    )) AS "AggJSonColA",
    jsonb_agg(jsonb_build_object(
        'key', b.key,
        'value', b.value
    )) AS "AggJsonColB"
FROM agg_col_a a
INNER JOIN agg_col_b b ON a.city = b.city
GROUP BY a.city
ORDER BY a.city;

逻辑说明 #

1. 用Postgres内置的jsonb_to_recordset函数把jsonb数组类型的列展开成多行多列的结构化数据，才能对数组内部的字段做求和等聚合操作
2. JColA按city+id分组，同个城市下同ID的amount求和，其余固定属性直接取最大值即可（同ID下这些属性值都是一致的）
3. JColB按city+key分组，把字符串类型的value转成数值求和后再转回字符串，和你需要的输出格式对齐
4. 最后用jsonb_agg把聚合后的结构化数据重新组装成jsonb数组，得到你要的结果

Postgres JSON操作学习建议 #

1. 先熟练核心常用函数：数组展开类（jsonb_to_recordset、jsonb_array_elements）、字段提取类（->、->>操作符，jsonb_extract_path）、构造类（jsonb_build_object、jsonb_build_array）、修改类（jsonb_set、jsonb_insert），这些覆盖了90%以上的日常JSON操作场景
2. 处理复杂JSON需求时优先用「拆成结构化数据计算，再重新组装成JSON」的思路，不要强行嵌套JSON函数实现，可读性和性能都更好
3. 平时可以多练几个小场景，比如数组求和、嵌套字段提取、JSON属性修改，熟练之后处理这类需求效率会高很多

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