PostgreSQL中关联含jsonb字段的表并合并对应JSON对象

需求说明 #

需要将activity_log表中jsonb类型的activity字段拆分，把其中type为USER的条目关联user表补充用户信息，type为COLLECTION的条目关联collection表补充集合信息，最后重组为指定的JSON结构返回。

假设依赖表结构 #

• user表：包含uuid（主键）、first_name、last_name字段
• collection表：包含uuid（主键）、title字段

实现SQL #

SELECT json_build_object(
    'activity', json_agg(
        CASE
            WHEN item.type = 'USER' THEN
                jsonb_build_object(
                    'type', item.type,
                    'uuid', item.uuid,
                    'first_name', u.first_name,
                    'last_name', u.last_name
                )
            WHEN item.type = 'COLLECTION' THEN
                jsonb_build_object(
                    'type', item.type,
                    'uuid', item.uuid,
                    'title', c.title
                )
            WHEN item.type = 'MESSAGE' THEN
                jsonb_build_object(
                    'type', item.type,
                    'name', 'Netflix'
                )
            ELSE item::jsonb
        END
    )
) AS result
FROM activity_log al,
     jsonb_to_recordset(al.activity) AS item(type text, uuid uuid, message text)
LEFT JOIN "user" u ON item.type = 'USER' AND item.uuid = u.uuid
LEFT JOIN collection c ON item.type = 'COLLECTION' AND item.uuid = c.uuid
GROUP BY al.uuid;

代码解释 #

1. 拆分JSON数组：通过jsonb_to_recordset(al.activity)将activity字段的JSON数组拆分为行数据，提取每条条目需要的type、uuid、message字段。
2. 关联外部表：使用LEFT JOIN分别关联user和collection表，仅当条目类型匹配时通过uuid关联，避免因无匹配数据丢失原始条目。
3. 重组JSON条目：利用CASE分支处理不同类型的条目：
   • USER类型：合并原始type、uuid与用户表的first_name、last_name
   • COLLECTION类型：合并原始type、uuid与集合表的title
   • MESSAGE类型：按需求构造包含type和name的对象（若需根据message模板动态获取名称，可新增模板表关联查询）
   • 其他类型：直接保留原始JSON结构
4. 聚合结果：用json_agg将处理后的条目重新聚合成数组，再通过json_build_object包装为外层的activity结构。
5. 分组返回：按activity_log的主键uuid分组，确保每条日志记录对应一个独立的结果对象。

注意事项 #

• 确保user、collection表的uuid字段类型与activity条目中的uuid一致（均为uuid类型），避免类型转换错误。
• 若MESSAGE类型的name需根据message字段动态映射，可创建模板表存储message键与对应名称，再通过关联查询替换固定值。

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