Mysql Nested json arrays

Question

I Have trouble to achive nested json array to the desired result

Current Code:

SELECT Json_arrayagg(Json_object('report_id', rc.report_id, 'user_id', 
                     rc.user_id, 
                            'text', rc.text, 
                            'created', rc.created,
                            'user', Json_object(
                                         'display_name', 
                                          u.display_name, 
                                          'photo_url', u.photo_url)
)) AS comments 
FROM   report_comments rc 
       JOIN users u 
         ON rc.user_id = u.id 
WHERE ` report_id ` = :reportId 

Current result:

[{comments: [{text: My first comment, user: {photo_url: www.example.com, display_name: Jay Jay}, created: 2021-01-03 00:47:58.000000, user_id: 2, report_id: 5}]}]

What i try to get:

{comments: [{text: My first comment, user: {photo_url: www.example.com, display_name: Jay Jay}, created: 2021-01-03 00:47:58.000000, user_id: 2, report_id: 5}, {text: My second comment, user: {photo_url: www.example.com, display_name: Jay Jay}, created: 2021-03-06 00:17:38.000000, user_id: 2, report_id: 5}]}

I would like to remove [] at start, so i could access the array by doing row["comments"] instead of row[0]["comments"]

Answer 1

等待大神解答