Is it possible to make a nested GROUP BY with SQL?

Question

I'm getting pretty close to the result i want, but i still can't figure out how to group by in the subquery. Here is my actual query, thanks to JNevill.

SELECT 
     JSON_OBJECT('id', a.IDartwork, 'creaDate', a.artworkCreationDate, 'length', a.length, 'summarize', a.summarize) AS artwork,
     JSON_ARRAYAGG(JSON_OBJECT('role', r.roleName, 'idCrea', c.IDcreator, 'nameCrea', c.creatorName)) as creators
     
FROM AMEGA_artwork a
    JOIN AMEGA_artwork_creator ac 
       ON a.IDartwork = ac.IDartwork
    JOIN AMEGA_creator c     
       ON c.IDcreator = ac.IDcreator 
    JOIN AMEGA_role r
       ON r.IDrole = ac.IDrole        
GROUP BY artwork;

And i got, that result :

[{"id": 1, "name": "Quentin Dupieux", "role": "Réalisateur"}, {"id": 10, "name": "Nikko", "role": "Acteur"}, {"id": 11, "name": "Gilles Lelouch", "role": "Acteur"}]

Which is close. But i would like to Group the creators by their role. I try to add the r.roleID in the GROUP BY, but i got separate rows by role as a result :

[{"id": 10, "name": "Nikko", "role": "Acteur"}, {"id": 11, "name": "Gilles Lelouch", "role": "Acteur"}]
[{"id": 1, "name": "Quentin Dupieux", "role": "Réalisateur"}]

I also try GROUP_CONCAT, with no result.

I need to melt those lignes to have one perfect result like that :

[{"role": "Réalisateur", [{"id": 1, "name": "Quentin Dupieux"}]}, {"role": "Acteur", [{"id": 10, "name": "Nikko"}, {"id": 11, "name": "Gilles Lelouch"}]}]

How to do that ? Is it even possible ?

Here is the MLD structure :