0

This SQL query gives me same values repeatedly:

select json_object('id',basics.resume_id, 
'work',JSON_ARRAYAGG(json_object('name', work.name, 'location', work.location, 
        'description', work.description, 'position', work.position, 'url', work.url, 
        'startDate', work.startDate, 'endDate', work.endDate, 'summary', work.summary, 
        'highlights', work.highlights, 'keywords', work.keywords)
        ),
'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

The value I get for it is:

{
"id":1,
"work":[
    {
        "url":"http://piedpiper.example.com",
        "name":"Pied Piper3",
        "endDate":"2014-12-01",
        "summary":"Pied Piper is a multi-platform technology based on a proprietary universal compression algorithm that has consistently fielded high Weisman Scores™ that are not merely competitive, but approach the theoretical limit of lossless compression.",
        "keywords":"Javascript,  React",
        "location":"Palo Alto, CA",
        "position":"CEO/President",
        "startDate":"2013-12-01",
        "highlights":"Build an algorithm for artist to detect if their music was violating copy right infringement laws,  Successfully won Techcrunch Disrupt,  Optimized an algorithm that holds the current world record for Weisman Scores",
        "description":"Awesome compression company"
    },
    {
        "url":" bnvc ",
        "name":"Pied Piper",
        "endDate":"vb nsncd",
        "summary":"bcbbv",
        "keywords":"nbdcnbsvd",
        "location":"nbdcnb",
        "position":"m m",
        "startDate":" vbn vb",
        "highlights":"jhsfcf ",
        "description":"mbvm"
    }
],
"awards":[
    {
        "title":"Digital Compression Pioneer Award"
    },
    {
        "title":"Digital Compression Pioneer Award"
    }
]}

I have added two rows of data in work table for resume_id = 1, but only 1 row of data in awards table for resume_id = 1

Improve this question
3
  • ... as expected. Imagine you had written "select * from basic left join work ... left join awards ...". Given that you have two work records with resume_id = 1, and one in awards, you'd expect two result records, with all the fields from basic, work, and awards. The work.* columns would be different, but the basic.* and awards.* columns would hold duplicate data in the two records. This is the data that the aggregator has to work with. At that point, it just aggregates the requested columns from the given data records. Hence, two copies of the award.* data. Commented Feb 5, 2022 at 22:22
  • So how do I make changes to the query, so that I get only one record Commented Feb 6, 2022 at 2:10
  • Simplest thing that might work for you is to add a "distinct": JSON_ARRAYAGG(DISTINCT JSON_OBJECT('title', awards.title)). This definitely works in Oracle, and may in mysql too, I'm not sure. Otherwise you might be looking at much more complicated sql, which would create the two lists in separate sub-queries that you then join together to collect the final json object. Also worth noting, on large datasets, using the "distinct" could be very inefficient. Commented Feb 6, 2022 at 12:52
Related questions
3
json showing duplicate output of mysql result
0
JSON from SQL Table get double result
0
Why is this piece of code giving me duplicate results?
Load 7 more related questions Show fewer related questions

0

Reset to default

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.