MySQL按相同ID聚合JSON对象为JSON数组的实现问题

你需要用MySQL的JSON_ARRAYAGG聚合函数来实现相同kurs.id下所有kompetenz对象的数组合并，同时通过GROUP BY分组确保每个kurs返回对应聚合后的结果。

基础解决方案（单分类场景） #

如果每个kurs仅对应一个kurskategorie，可以使用以下查询：

SELECT
    kurs.id,
    kurs.Kursname,
    kurs.Kursbeschreibung,
    JSON_OBJECT('id', kkat.id, 'spalte', kkat.spalte, 'kurskategoriename', kkat.kurskategoriename) AS kurskategorie,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', komp.id, 'kompetenzname', komp.Kompetenzname)
    ) AS kurskompetenzen_erlerndend,
    kurs.link
FROM
    wipro_hs22.kurs
JOIN wipro_hs22.kurs_kurskategorie AS k_k ON kurs.id = k_k.idKurs
JOIN wipro_hs22.kurskategorie AS kkat ON k_k.idKurskategorie = kkat.id
JOIN wipro_hs22.kurs_kompetenz AS k_ko ON kurs.id = k_ko.idKurs
JOIN wipro_hs22.kompetenz AS komp ON k_ko.idKompetenz = komp.id
GROUP BY
    kurs.id, kurs.Kursname, kurs.Kursbeschreibung, kkat.id, kkat.spalte, kkat.kurskategoriename, kurs.link;

关键修改说明 #

1. 移除原查询中的DISTINCT，改用GROUP BY按kurs的所有非聚合字段（以及kurskategorie的字段）分组，确保每个分组对应单个kurs+单个kurskategorie的组合。
2. 将原查询中生成单个对象数组的JSON_ARRAY替换为JSON_ARRAYAGG，该函数会自动将分组内的所有kompetenzJSON对象合并成一个数组。

多分类场景扩展（可选） #

如果一个kurs对应多个kurskategorie，可以同时将分类也聚合为数组，让每个kurs仅返回一行结果：

SELECT
    kurs.id,
    kurs.Kursname,
    kurs.Kursbeschreibung,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', kkat.id, 'spalte', kkat.spalte, 'kurskategoriename', kkat.kurskategoriename)
    ) AS kurskategorien,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', komp.id, 'kompetenzname', komp.Kompetenzname)
    ) AS kurskompetenzen_erlerndend,
    kurs.link
FROM
    wipro_hs22.kurs
JOIN wipro_hs22.kurs_kurskategorie AS k_k ON kurs.id = k_k.idKurs
JOIN wipro_hs22.kurskategorie AS kkat ON k_k.idKurskategorie = kkat.id
JOIN wipro_hs22.kurs_kompetenz AS k_ko ON kurs.id = k_ko.idKurs
JOIN wipro_hs22.kompetenz AS komp ON k_ko.idKompetenz = komp.id
GROUP BY
    kurs.id, kurs.Kursname, kurs.Kursbeschreibung, kurs.link;

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