Can't figure out a triple joint with group by

Question

I want to get the Creators of Artworks, GROUP BY by their role. I find a way to have the list of the Creators with their role, for each Artwork.

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

But I can't get to use the GROUP BY. I get the error:

Subquery returns more than 1 row

I can't find a solution. I want a result like that :

artworks         creators
 <art1>          < <"director", <crea1, crea26>>, <"writer", <crea5, crea12>> >
 <art2>          < <"drawer", <crea23, crea8>> >

Here is the MLD structure :

Any idea ?

Answer 1

Here's a bit of a guess on how this should look from my comment. Essentially somewhere in your SQL (either via correlated subquery's where clause or through a single SELECT's FROM clause) you have to establish a relationship between AMEGA_artwork and the rest of the tables. You went with a correlated subquery which is a good choice. The issue though is that for each AMEGA_artwork record there is more than one aggregated JSON_ARRAYAGG() result in that subquery. Normally this would be fine, but because your subquery is in your SELECT clause you can only have a single record come through for each AMEGA_artwork record. Otherwise you get that error.

I've switched this to a single FROM clause to establish the relationships between your table, and then a single JSON_OBJECT leading to your artwork output column and an aggregated JSON_ARRAYAGG() of the remaining column.

SELECT 
     JSON_OBJECT('id', a.IDartwork, 'creaDate', a.artworkCreationDate, 'length', a.length, 'summarize', a.summarize) AS artwork,
     JSON_ARRAYAGG(JSON_OBJECT('id', c.IDcreator, 'name', c.creatorName, 'role', r.roleName)
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;

The different here is that we establish the relationship between AMEGA_artwork and AMEGA_artwork_creator in the FROM clause.

What you will likely see on running this is more than one record for each artwork. Which one is correct, or which one you want to keep, or how you want to further aggregate the data to reduce it to a single record, will be up to you.