0

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

4
  • Does your data come back as nulls for awards as you are expecting, without the JSON_ARRAYAGG(). Can you post the output of your query using only the Ids? Commented Feb 6, 2022 at 3:46
  • Please, edit your post and give table schema and sample datas Commented Feb 6, 2022 at 11:26
  • Try '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. Commented Feb 6, 2022 at 20:10
  • @h.m.i.13 DISTINCT did not work. MySQL produced an error. Commented Feb 10, 2022 at 9:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.