如何将JSON对象行转换为MySQL中的JSON数组？

发布于 2025-02-04 19:51:49 字数 717 浏览 5 评论 0原文

卡在一个锻炼任务中，我想要json对象的形式为json array。实际上，我已经达到了第一阶段，将事物转换为JSON对象。

第二阶段是我的输出应与下面的预期输出一样相同，但并非如此。

当前输出：

{"name": "Peter", "uniqueId": "1"}
{"name": "MaryChan", "uniqueId": "3"}

预期输出：

[{"name": "Peter", "uniqueId": "1"}, {"name": "MaryChan", "uniqueId": "3"}]

查询：

SELECT JSON_OBJECT('uniqueId', uniqueId, 'name', name) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

有些人建议使用group_concat将其完成，但我仍然无法实现预期格式。

欢迎任何帮助或指针。

原文

Stuck in one exercise task where I want JSON objects to be in form of a JSON array. Actually I have reached up to 1st stage to convert things into JSON object first.

Second stage is where my output should be as same as expected output as below, but it is not coming.

Current output :

{"name": "Peter", "uniqueId": "1"}
{"name": "MaryChan", "uniqueId": "3"}

Expected output :

[{"name": "Peter", "uniqueId": "1"}, {"name": "MaryChan", "uniqueId": "3"}]

Query :

SELECT JSON_OBJECT('uniqueId', uniqueId, 'name', name) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Some have suggested to use GROUP_CONCAT to get it done but still I'm not able to achieve the expected format.

Any help or pointers are welcomed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ヅ她的身影、若隐若现 2025-02-11 19:51:49

您可以使用mySQL 聚合函数：

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

尝试在这里。

You can use MySQL JSON_ARRAYAGG aggregation function:

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Try it here.

回复收藏 0 原文

~没有更多了~