Mysql Inner Join and Group By repeating row

Question

I have problem with that more comments the comment table has. Then the more the User table repeating it value in the report group. It should only fetch user once, while comments as many there is which it is the current behaviour. But I would like the user being fetched once.

How it's now:

["text": "My first report",
user: [{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png",
{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"},
{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"}}],
comments: [{"text": "Great Report", display_name: "Xavier"},
{"text": "Bad Report", display_name: "Logan"},
{"text": "Thanks for the feedback", display_name: "Cyclops"}]]

What i expect:

["text": "My first report",
user: [{"display_name": "Cyclops", "photo_url": "CYCLOPS_IMAGE.png"}],
comments: [{"text": "Great Report", display_name: "Xavier"},
{"text": "Bad Report", display_name: "Logan"},
{"text": "Thanks for the feedback", display_name: "Cyclops"}]]

Code:

SELECT report.text, 
       Json_arrayagg(Json_object('display_name', users.display_name, 'photo_url', users.photo_url)) AS USER, 
       Json_arrayagg(Json_object('text', report_comments.text, 'display_name', report_comments.user_id)) AS COMMENTS 
FROM   report 
       INNER JOIN users 
               ON users.id = report.user_id 
       LEFT JOIN report_comments 
              ON report_comments.report_id = report.id 
WHERE  report.user_id = :userId 
GROUP  BY report.id 

question from:https://stackoverflow.com/questions/65545515/mysql-inner-join-and-group-by-repeating-row

Answer 1

Assuming report to users is a 1:1 relationship, then you should be able to resolve your issue by doing the comment aggregation in a derived table, and joining to that instead:

SELECT report.text, 
       Json_arrayagg(Json_object('display_name', users.display_name, 'photo_url' 
                     , 
                     users.photo_url))                       AS USER,
       rc.COMMENTS
FROM   report 
INNER JOIN users ON users.id = report.user_id 
LEFT JOIN (
  SELECT report_id,
         Json_arrayagg(Json_object('text', report_comments.text, 'display_name', 
                                   report_comments.user_id)) AS COMMENTS
  FROM report_comments 
  GROUP BY report_id
) rc ON rc.report_id = report.id 
WHERE  report.user_id = :userId 
GROUP  BY report.id 

If a report can have multiple users, you will need to aggregate the user information in a derived table in the same manner.