PostgreSQL多对多关系下如何加载并分组关联记录

原查询采用关联子查询生成标签数组，不仅维护成本高（比如添加标签过滤需嵌套修改），数据量大时性能也会受影响。以下是两种更优的实现方式，兼顾性能与可维护性：

方式一：使用CTE预聚合标签（推荐） #

先通过公共表表达式（CTE）一次性聚合所有item的标签数据，再与items表关联后按group_id聚合，逻辑清晰且性能更优：

WITH item_tags AS (
    SELECT 
        ti.item_id,
        json_agg(json_build_object('id', t.tag_id, 'label', t.tag_name)) AS tags
    FROM tags_items ti
    LEFT JOIN tags t ON ti.tag_id = t.tag_id
    -- 标签过滤条件直接加在这里，比如：WHERE t.tag_name = 'xxx'
    GROUP BY ti.item_id
)
SELECT 
    i.group_id,
    json_agg(json_build_object(
        'item_id', i.item_id,
        'item_name', i.item_name,
        'tags', COALESCE(it.tags, '[]'::json) -- 处理无标签的item，返回空数组
    )) AS items
FROM items i
LEFT JOIN item_tags it ON i.item_id = it.item_id
GROUP BY i.group_id

优势： #

• 可维护性：标签过滤、结构修改仅需在CTE部分调整，无需嵌套到外层聚合逻辑中；
• 性能：一次性扫描tags_items和tags表完成标签聚合，避免原查询中每个item触发一次子查询的开销；
• 兼容性：支持PostgreSQL 9.4及以上版本（json_agg和CTE的标准支持版本）。

方式二：使用LATERAL JOIN（灵活场景） #

如果需要针对单个item做更复杂的标签处理（比如限制标签数量、按标签名称排序），LATERAL JOIN会更灵活：

SELECT 
    i.group_id,
    json_agg(json_build_object(
        'item_id', i.item_id,
        'item_name', i.item_name,
        'tags', it.tags
    )) AS items
FROM items i
LEFT JOIN LATERAL (
    SELECT json_agg(json_build_object('id', t.tag_id, 'label', t.tag_name) ORDER BY t.tag_name) AS tags
    FROM tags_items ti
    LEFT JOIN tags t ON ti.tag_id = t.tag_id
    WHERE ti.item_id = i.item_id
    -- 可添加标签过滤或数量限制，比如：LIMIT 5
) it ON true
GROUP BY i.group_id

优势： #

• 灵活性：可以为每个item定制标签聚合逻辑（如排序、限制数量）；
• 性能：配合tags_items(item_id)索引，查询效率接近CTE方案。

索引优化建议 #

为进一步提升查询性能，建议添加以下索引：

• 给tags_items(item_id, tag_id)创建复合索引：加速按item_id聚合标签的操作；
• 给tags(tag_name)创建索引：如果经常通过标签名称过滤数据；
• 给items(group_id)创建索引：加速按group_id的分组聚合。

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