Postgres jsonb列按城市分组聚合求和及json格式化资源咨询

核心逻辑是先将jsonb数组拆分为独立元素做聚合计算，再重新组装为数组，完整实现代码如下：

-- 测试表结构（供复现使用）
CREATE TABLE city_data (
    City text,
    JColA jsonb,
    JColB jsonb
);

-- 最终查询语句
WITH 
-- 拆分JColA并按id聚合金额
unpacked_a AS (
    SELECT 
        City,
        (a->>'id')::int AS id,
        a->>'name' AS name,
        a->>'type' AS type,
        SUM((a->>'amount')::numeric) AS total_amount
    FROM city_data,
         jsonb_array_elements(JColA) AS a
    GROUP BY City, id, name, type
),
-- 拆分JColB并按key聚合值
unpacked_b AS (
    SELECT 
        City,
        (b->>'key') AS key,
        SUM((b->>'value')::numeric) AS total_value
    FROM city_data,
         jsonb_array_elements(JColB) AS b
    GROUP BY City, key
),
-- 组装AggJSonColA
agg_a AS (
    SELECT 
        City,
        jsonb_agg(
            jsonb_build_object(
                'id', id,
                'name', name,
                'type', type,
                'amount', total_amount
            )
        ) AS AggJSonColA
    FROM unpacked_a
    GROUP BY City
),
-- 组装AggJsonColB
agg_b AS (
    SELECT 
        City,
        jsonb_agg(
            jsonb_build_object(
                'key', key,
                'value', total_value
            )
        ) AS AggJsonColB
    FROM unpacked_b
    GROUP BY City
)
SELECT 
    COALESCE(agg_a.City, agg_b.City) AS City,
    COALESCE(AggJSonColA, '[]'::jsonb) AS AggJSonColA,
    COALESCE(AggJsonColB, '[]'::jsonb) AS AggJsonColB
FROM agg_a
FULL OUTER JOIN agg_b ON agg_a.City = agg_b.City;

注意事项 #

• 上述代码用FULL OUTER JOIN兼容部分City只有单列数据的场景，COALESCE会将空值替换为空数组，避免返回NULL
• 若相同id对应多个不同的name/type，可根据业务调整分组逻辑：仅按City和id分组，name/type字段用MAX(name)取任意值，或用jsonb_agg(DISTINCT name)合并多值
• 数值类型转换可根据实际存储调整，比如整数类型可以将::numeric改为::int

• PostgreSQL官方文档JSON函数章节：覆盖所有内置json/jsonb运算符、函数的用法与示例，是最权威的参考资料
• 《PostgreSQL实战》JSON类型相关章节：包含大量生产环境的jsonb操作、性能优化、GIN索引使用的实战案例
• PostgreSQL官方Wiki JSONB板块：收录常见问题解答、最佳实践、性能对比测试等内容
• 国内PostgreSQL社区技术博客：有大量开发者分享的不同业务场景下的jsonb实操教程

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