Apologies for the bad wording of the question, I couldn’t think of a way to phrase it better. For instance, let’s say my schema is something like this, in pseudocode:

table post {
 // removed for brevity
}
table attachment {
 // removed for brevity
 attachmentType: "img" | "video";
 postId: reference => post

Now, what I’m looking to create a query where the final scaffolding I’ll end up with is like this:

const Post = {
 // removed for brevity;
 imgAttachments: Attachment[];
 videoAttachments: Attachment[];
}

Here’s the query in question:

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 id is

Now this query works fine. But, what I’m wondering if it is possible to reduce this query to only one left join, and then, depending on whether it’s an image or video attachment, to assign it to its correct column in the rows that are going to be returned. Or perhaps the performance gains would be marginal regardless?