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 ?
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 eachAMEGA_artwork
record there is more than one aggregatedJSON_ARRAYAGG()
result in that subquery. Normally this would be fine, but because your subquery is in yourSELECT
clause you can only have a single record come through for eachAMEGA_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.The different here is that we establish the relationship between
AMEGA_artwork
andAMEGA_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.