Data get repeated with json_arrayagg

Question

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");

the work filed gives the output as the second comment . by doubling the same data. — rishabharidas
– rishabharidas, Commented Feb 5, 2022 at 9:22
Please do not (directly) comment on your own question. you should use edit for that. — Luuk
– Luuk, Commented Feb 5, 2022 at 9:28
Fix the tags, MySQL is completely different from PostgreSQL. Only use the tag for the DBMS that you are actually using (including version numbering because of the differences between MySQL/MariaDB) — Luuk
– Luuk, Commented Feb 5, 2022 at 9:28
Some sample (input) data, to have a minimal reproducible example, would be nice! — Luuk
– Luuk, Commented Feb 5, 2022 at 9:30
Thanks for doing the edits. Unfortunate with the given data it is not possible to reproduce the problem, see my answer — Luuk
– Luuk, Commented Feb 5, 2022 at 9:55

Luuk · Accepted Answer · 2022-02-07 16:46:06Z

1

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;

edited Feb 7, 2022 at 16:46

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

answered Feb 7, 2022 at 4:50

rishabharidas

536 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

The query in the question doesn't have a join.

Luuk · Accepted Answer · 2022-02-05 09:50:38Z

0

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

edited Feb 5, 2022 at 9:50

answered Feb 5, 2022 at 9:38

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

3 Comments

rishabharidas Over a year ago

thanks.. this thing works. i dont understand where is my codes issue.

Luuk Over a year ago

It might have to do with versions? What is the output of this: select version(); ?

rishabharidas Over a year ago

8.0.28-0ubuntu0.20.04.3

Collectives™ on Stack Overflow

Data get repeated with json_arrayagg

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related