CODEWITHSUNDEEP
mysqlsql
rishabharidas
rishabharidas

Reputation: 53

Data get repeated with json_arrayagg

The output getting from the query id repeated it the data inside is multiple

select json_arrayagg(json_object("network", basics_profiles.network, "username", basics_profiles.username, "url", basics_profiles.url)) as profiles from basics_profiles;

Ok here when i run this code the in work section even when there it is only 2 data. it get doubled. if there was only single data. then there is no issue.

output while there is more tan 1 data:

[
    {
        "url": "twitter.com/naruto",
        "network": "Twitter",
        "username": "uzumakinaruto"
    },
    {
        "url": "twitter.com/naruto",
        "network": "Twitter",
        "username": "uzumakinaruto"
    },
    {
        "url": "instagram.com/naruto",
        "network": "Instagram",
        "username": "uzumakinaruto"
    },
    {
        "url": "instagram.com/naruto",
        "network": "Instagram",
        "username": "uzumakinaruto"
    }
]

i wanted something like this

[
    {
        "url": "twitter.com/naruto",
        "network": "Twitter",
        "username": "uzumakinaruto"
    },
    {
        "url": "instagram.com/naruto",
        "network": "Instagram",
        "username": "uzumakinaruto"
    }
]

u can try with the thsi table:

create table basics_profiles(network text, url text, username text);

insert into basics_profiles values("twitter","jhaajdka.com","naruto");

insert into basics_profiles values("instagram","jhasdasdasdsdd.com","sasuke");

Upvotes: 0

Views: 402

Answers (2)

rishabharidas
rishabharidas

Reputation: 53

Thanks for Helping me out. i got my issue. With the about code it was showing all the data in a single row if i had multiple row of data, for me the issue was when joining 2 tables the data get repeated again and again.

select 
   basics_information.*,
   (select json_arrayagg(json_object("name", interests.name, "keywords", interests.keywords)) 
    from interests 
    where basics_information.id = interests.resumeId) as interests 
from basics_information 
left join interests on basics_information.id = interests.resumeId 
group by basics_information.id;

Upvotes: 1

Luuk
Luuk

Reputation: 14899

With the given info, you could try (could not test because of missing input data):

select 
   json_arrayagg(DISTINCT json_object("network", basics_profiles.network, 
                             "username", basics_profiles.username, 
                             "url", basics_profiles.url)
                ) as profiles 
from basics_profiles;

EDIT:

  • With MariaDB 10.6 the DISTINCT is not even needed, see: dbfiddle
  • With MySQL8.0 the DISTINCT is not even needed, see: dbfiddle

This proves that input data is needed to reproduce the problem

Upvotes: 0

Related Questions