按角色分组查询艺术品创作者时遇子查询返回多行错误求解决

问题 #

需要获取艺术品的创作者信息，并按创作者角色进行分组。目前已编写SQL语句可查询每件艺术品对应的创作者及其角色列表，但尝试使用GROUP BY时出现Subquery returns more than 1 row错误，无法得到预期的按角色聚合创作者的结果。

现有SQL代码：

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;

预期结果：

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

解决方法 #

要实现按角色分组聚合创作者，需要先按角色分组生成每个角色对应的创作者数组，再把这些角色-创作者的对象聚合起来。修改后的SQL如下：

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

关键说明 #

• 内层查询先通过GROUP BY r.roleName按角色分组，对每个角色下的创作者用JSON_ARRAYAGG生成创作者数组。
• 再用JSON_OBJECTAGG把角色名作为键、创作者数组作为值，生成键值对形式的JSON对象，完全匹配预期的按角色分组结构。
• 原错误是因为直接在子查询中使用GROUP BY但未正确聚合返回结果，现在通过先分组聚合创作者、再生成角色映射JSON的方式，避免了子查询返回多行的问题。

内容的提问来源于stack exchange，提问作者nikubik