我试图在MySQL
5.7.12版(本手册的13.16节)中仅使用本机JSON功能(无PHP等),我试图编写一个查询以从包含子对象的关系表中生成JSON文档。给出以下示例:
CREATE TABLE `parent_table` (
`id` int(11) NOT NULL,
`desc` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
`id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
`desc` varchar(20) NOT NULL,
PRIMARY KEY (`id`,`parent_id`)
);
insert `parent_table` values (1,'parent row 1');
insert `child_table` values (1,1,'child row 1');
insert `child_table` values (2,1,'child row 2');
我正在尝试生成如下所示的JSON文档:
[{
"id" : 1,
"desc" : "parent row 1",
"child_objects" : [{
"id" : 1,
"parent_id" : 1,
"desc" : "child row 1"
}, {
"id" : 2,
"parent_id" : 1,
"desc" : "child row 2"
}
]
}]
我是MySQL的新手,并且怀疑有一种SQL模式可以从一对多关系生成嵌套的JSON对象,但是我很难找到它。
在Microsoft SQL(我更熟悉)中,它的工作原理如下:
select
[p].[id]
,[p].[desc]
,(select * from [dbo].[child_table] where [parent_id] = [p].[id] for json auto) AS [child_objects]
from [dbo].[parent_table] [p]
for json path
我试图在MySQL中编写等效的代码,如下所示:
select json_object(
'id',p.id
,'desc',p.`desc`
,'child_objects',(select json_object('id',id,'parent_id',parent_id,'desc',`desc`)
from child_table where parent_id = p.id)
)
from parent_table p;
select json_object(
'id',p.id
,'desc',p.`desc`
,'child_objects',json_array((select json_object('id',id,'parent_id',parent_id,'desc',`desc`)
from child_table where parent_id = p.id))
)
from parent_table p
两次尝试均失败,并出现以下错误:
Error Code: 1242. Subquery returns more than 1 row