Mysql JSON_OBJECT aggregate json objects to json array where id matches

Question

As mentioned in the title, i've been trying to aggregate json objects to a json array where the id is the same. I've got a main table "kurs" that has a unique id and is connected to the table "kompetenz" over a link table. For each "kurs" there can be multiple "kompetenzen". However after a few hours of trying i still couldnt get it to work.

this is the link table

This is my query:

SELECT DISTINCT
    kurs.id,
    kurs.Kursname,
    kurs.Kursbeschreibung,
    JSON_OBJECT('id',
            kkat.id,
            'spalte',
            kkat.spalte,
            'kurskategoriename',
            kkat.kurskategoriename) AS kurskategorie,
    JSON_ARRAY(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;

the result im getting is following:

but i want to aggregate all the json objects in the column "kurskompetenzen_erlerndend" to a json_array where the id at the front of the result set is the same. I don't know what im missing, i'm not experienced with sql. Hope anyone can help, thanks in advance :)

Answer 1

Found the answer myself shortly after i thought i won't be able to solve it.

Worked with this query:

SELECT DISTINCT
    kurs.id,
    kurs.Kursname,
    kurs.Kursbeschreibung,
    JSON_OBJECT('id',
            kkat.id,
            'spalte',
            kkat.spalte,
            'kurskategoriename',
            kkat.kurskategoriename) AS kurskategorie,
    (SELECT 
            JSON_ARRAYAGG(JSON_OBJECT('id',
                                komp.id,
                                'kompetenzname',
                                komp.Kompetenzname))
        FROM
            wipro_hs22.kompetenz AS komp
                JOIN
            wipro_hs22.kurs_kompetenz AS k_komp ON komp.id = k_komp.idKompetenz
        WHERE
            k_komp.idKurs = kurs.id) 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;