PostgreSQL能否通过单左连接按附件类型拆分JSON聚合列？

需求说明 #

表结构（伪代码） #

table post {
 // 省略其他字段
}
table attachment {
 // 省略其他字段
 attachmentType: "img" | "video";
 postId: reference => post
}

期望返回结构（TypeScript伪代码） #

const Post = {
 // 省略其他字段;
 imgAttachments: Attachment[];
 videoAttachments: Attachment[];
}

当前已实现的查询 #

SELECT 
    post.id, 
    imgAttachments.att as "imgAttachments", 
    videoAttachments.att as "videoAttachments"
FROM post
LEFT JOIN (
  SELECT 
  a.post_id,
  jsonb_agg(
      jsonb_build_object(
          'id', a.id,
          'attachment_type', a.attachment_type
      )
  ) att
    FROM attachment a WHERE a.attachmentType = 'img'
    GROUP BY a.post_id
) imgAttachments ON imgAttachments.post_id=post.id
LEFT JOIN (
  SELECT 
  a.post_id,
  jsonb_agg(
      jsonb_build_object(
          'id', a.id,
          'attachment_type', a.attachment_type
      )
  ) att
    FROM attachment a WHERE a.attachmentType = 'video'
    GROUP BY a.post_id
) videoAttachments ON videoAttachments.post_id=post.id
WHERE post.id='whatever this post is'

用户问题 #

是否可以将该查询简化为仅一个LEFT JOIN，根据attachmentType的值将聚合结果分配到对应的imgAttachments和videoAttachments列？或是这样的优化带来的性能提升微乎其微？

解决方案：单LEFT JOIN实现分类聚合 #

完全可以通过条件聚合实现只关联一次attachment表，利用PostgreSQL的FILTER子句（推荐）或CASE表达式分别聚合不同类型的附件：

最优写法（PostgreSQL 9.4+支持FILTER） #

SELECT 
    post.id,
    -- 聚合img类型附件
    jsonb_agg(
        jsonb_build_object(
            'id', a.id,
            'attachment_type', a.attachment_type
        )
    ) FILTER (WHERE a.attachmentType = 'img') AS "imgAttachments",
    -- 聚合video类型附件
    jsonb_agg(
        jsonb_build_object(
            'id', a.id,
            'attachment_type', a.attachment_type
        )
    ) FILTER (WHERE a.attachmentType = 'video') AS "videoAttachments"
FROM post
LEFT JOIN attachment a ON a.post_id = post.id
WHERE post.id = 'whatever this post is'
GROUP BY post.id;

兼容低版本写法（用CASE表达式） #

如果你的PostgreSQL版本不支持FILTER，可以用CASE替代，但需要额外清理null值：

SELECT 
    post.id,
    jsonb_strip_nulls(
        jsonb_agg(
            CASE WHEN a.attachmentType = 'img' THEN
                jsonb_build_object(
                    'id', a.id,
                    'attachment_type', a.attachment_type
                )
            END
        )
    ) AS "imgAttachments",
    jsonb_strip_nulls(
        jsonb_agg(
            CASE WHEN a.attachmentType = 'video' THEN
                jsonb_build_object(
                    'id', a.id,
                    'attachment_type', a.attachment_type
                )
            END
        )
    ) AS "videoAttachments"
FROM post
LEFT JOIN attachment a ON a.post_id = post.id
WHERE post.id = 'whatever this post is'
GROUP BY post.id;

性能分析 #

1. 性能差异：
   • 原查询需要对attachment表扫描两次（两个子查询各一次），优化后的查询只扫描一次，当attachment表数据量较大时，能明显减少IO开销和重复分组计算，性能提升显著。
   • 若attachment表数据量极小，两者性能差异不大，但优化后的代码更简洁，维护成本更低。
2. 索引建议：
   给attachment表的post_id和attachmentType建立联合索引：

   CREATE INDEX idx_attachment_post_type ON attachment(post_id, attachmentType);
   

   无论哪种写法，该索引都能大幅提升过滤和聚合的效率。

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