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 🙂
Advertisement
Answer
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;