用json_arrayagg重复数据

Question

从查询id中得到的输出重复它内部的数据是多个

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

好的，在这里，当我运行这段代码时，在工作部分，即使只有2个数据。它会加倍。如果只有单一的数据。那就没有问题了。

输出时，有更多的tan 1数据：

[
    {
        "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"
    }
]

我想要这样的东西

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

你可以试试这张桌子：

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

Answer 1

谢谢你帮我。我有问题了。使用about代码，如果我有多行数据，它将显示单个行中的所有数据，对我来说，问题是当连接两个表时，数据会被一次又一次地重复。

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;

Answer 2

对于给定的信息，您可以尝试(由于缺少输入数据而无法进行测试)：

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

编辑：

• 对于MariaDB 10.6，甚至不需要区分，请参见：[医]小提琴
• 使用MySQL8.0时，甚至不需要不同的内容，请参阅：[医]小提琴

--这证明了重现问题需要输入数据。