This is what i need to achieve:
{"id":1,
"work":[
{
"name":"Pied Piper3"
},
{
"name":"Pied Piper"
}
],
"awards":[
{
"title":"Digital Compression Pioneer Award"
}
]}
This is what i am getting
{"id":1,
"work":[
{
"name":"Pied Piper3"
},
{
"name":"Pied Piper"
}
],
"awards":[
{
"title":"Digital Compression Pioneer Award"
},
{
"title":"Digital Compression Pioneer Award"
}
]}
My query:
select json_object('id',basics.resume_id,
'work',JSON_ARRAYAGG(json_object('name', work.name)),
'awards', JSON_ARRAYAGG(JSON_OBJECT('title', awards.title))
) from basics
left join work on basics.resume_id = work.resume_id
left join awards on basics.resume_id = awards.resume_id where basics.resume_id = 1
I have added two rows of work for resume_id = 1 but only 1 row of awards for resume_id = 1.But I still get as many number of awards in result as per the number of works for resume_id =1
'awards', JSON_ARRAYAGG(DISTINCT JSON_OBJECT('title', awards.title)), this would work in mariadb, in mysql I'm not sure, or are you using postgresql? Please set the correct tags and only the relevant.