这是一个基于 CTE 的解决方案，它聚合 CTE 中的用户、图像和评论信息，然后将所有这些 CTE 加入报告将所有数据收集在一起的表格:

Here's a CTE-based solution which aggregates the user, image and comment information in CTEs and then joins all those CTEs to the report table to collect all the data together:

WITH user AS (
  SELECT id, JSON_ARRAYAGG(JSON_OBJECT('display_name', u.display_name, 'photo_url', u.photo_url)) AS user
  FROM users u
  WHERE id = 4
),
img AS (
  SELECT report_id, JSON_ARRAYAGG(JSON_OBJECT('image_path', image_path)) AS images
  FROM report_images
  GROUP BY report_id
),
cmt AS (
  SELECT report_id, 
         JSON_ARRAYAGG(JSON_OBJECT('text', rc.text, 'display_name', u.display_name)) AS comments,
         COUNT(*) AS totalcomments
  FROM report_comments rc
  JOIN users u ON rc.user_id = u.id
  GROUP BY report_id
)
SELECT r.text,
       u.user,
       img.images,
       cmt.totalcomments,
       cmt.comments
FROM report r
JOIN user u ON u.id = r.user_id
LEFT JOIN img ON img.report_id = r.id
LEFT JOIN cmt ON cmt.report_id = r.id

dbfiddle 演示