PostgreSQL中合并同id下相同键的jsonb数据方法

完整SQL实现 #

假设你的表名为permissions_table，可以用以下SQL语句实现需求：

SELECT 
    id,
    jsonb_build_object(
        'users', '{}'::jsonb,
        'groups', jsonb_object_agg(group_key, combined_array),
        'clients', '{}'::jsonb
    ) AS permissions
FROM (
    SELECT 
        id,
        group_key,
        jsonb_agg(elem) AS combined_array
    FROM (
        SELECT 
            id,
            jsonb_object_keys(permissions->'groups') AS group_key,
            unnest(jsonb_array_elements_text(permissions->'groups'->jsonb_object_keys(permissions->'groups'))) AS elem
        FROM permissions_table
    ) AS group_elements
    GROUP BY id, group_key
) AS grouped_data
GROUP BY id;

逻辑拆解 #

1. 拆解JSONB数组为行记录
   最内层子查询通过jsonb_object_keys提取groups中的键名（如ReadKey、ExportKey），再用jsonb_array_elements_text将对应键的数组拆分为单个文本元素，最后用unnest将数组元素展开为独立行，得到id、分组键、单个数组元素的扁平结构。

2. 按ID和分组键聚合数组
   中间层子查询按id和分组键分组，使用jsonb_agg将单个元素重新聚合为数组，得到每个ID下每个分组键对应的合并后数组。

3. 重构完整JSONB结构
   最外层通过jsonb_object_agg将分组键与合并后的数组重构为groups对象，再用jsonb_build_object组合users（空对象）、groups、clients（空对象），生成最终的permissions字段。

扩展说明 #

如果后续users或clients字段也需要合并数组，只需参照groups的处理逻辑，添加对应的拆解和聚合步骤即可。

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