如何使用Postgres JSON函数获取关联标签的聚合JSON对象

实现方案 #

核心思路 #

• 先找到每个标签关联的所有帖子ID
• 匹配这些帖子下关联的所有其他标签（排除标签自身）
• 对关联标签按tag_type分组，聚合为要求的JSON结构，同时对重复关联的标签做去重处理

完整查询SQL #

SELECT
    t.name,
    t.tag_type,
    -- 无关联标签时返回空对象，可根据需求去掉COALESCE返回null
    COALESCE(jsonb_object_agg(
        related_tag_type,
        related_tag_names
    ), '{}'::jsonb) AS related
FROM tags t
LEFT JOIN (
    -- 子查询：计算每个原标签对应的关联标签信息，去重避免同标签对多帖子关联重复展示
    SELECT DISTINCT
        tp1.tag_id AS original_tag_id,
        t2.tag_type AS related_tag_type,
        json_agg(json_build_object('name', t2.name)) OVER (PARTITION BY tp1.tag_id, t2.tag_type) AS related_tag_names
    FROM tags_posts tp1
    -- 关联同一帖子下的其他标签关系
    JOIN tags_posts tp2 ON tp1.post_id = tp2.post_id AND tp1.tag_id != tp2.tag_id
    -- 关联获取关联标签的属性
    JOIN tags t2 ON tp2.tag_id = t2.id
) r ON t.id = r.original_tag_id
GROUP BY t.id, t.name, t.tag_type
ORDER BY t.id;

关键逻辑说明 #

• json_build_object('name', t2.name)：将关联标签的name字段构造成{"name": "xxx"}格式的JSON对象
• json_agg(...) OVER (PARTITION BY tp1.tag_id, t2.tag_type)：将同一原标签下、同类型的关联标签对象聚合为数组
• jsonb_object_agg(...)：将tag_type作为key，对应的标签数组作为value，聚合为最终的JSON对象
• 子查询的DISTINCT用来处理同一个标签对在多个帖子下重复关联的问题，避免最终结果出现重复的标签对象

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