如何使用 mysql 原生 json 函数生成嵌套的 json 对象?

2021-11-20 00:00:00 json mysql

在 MySQL 5.7.12 版(手册中的第 13.16 节)中仅使用本机 JSON 功能(无 PHP 等)我正在尝试编写查询以从包含子对象的关系表生成 JSON 文档.给出以下示例:

Using only the native JSON fuctions (no PHP, etc) in MySQL version 5.7.12 (section 13.16 in the manual) I am trying to write a query to generate a JSON document from relational tables that contains a sub object. Given the following example:

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 文档:

I am trying to generate a JSON document that looks like this: 

[{
    "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 对象，但我找不到它.

I am new to MySQL and suspect there is a SQL pattern for generating nested JSON objects from one to many relationships but I'm having trouble finding it.

在 Microsoft SQL(我更熟悉)中，以下工作:

In Microsoft SQL (which I'm more familiar with) the following works: 

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 中编写如下等价物:

I attempted to write the equivalent in MySQL as follows: 

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

两次尝试都失败并显示以下错误:

Both attempts fail with the following error:

Error Code: 1242. Subquery returns more than 1 row

推荐答案

您收到这些错误的原因是父 json 对象不期望结果集作为其输入之一，您需要有简单的对象对，例如{name, string} 等 错误报告 - 可能在未来的功能中可用... 这只是意味着您需要将多行结果转换为以逗号分隔的结果的串联，然后转换为 json 数组.

The reason you are getting these errors is that the parent json object is not expecting a result set as one of its inputs, you need to have simple object pairs like {name, string} etc bug report - may be available in future functionality... this just means that you need to convert your multi row results into a concatination of results separated by commas and then converted into a json array.

你的第二个例子差点就搞定了.

You almost had it with your second example.

您可以使用 GROUP_CONCAT 功能实现您的目标

You can achieve what you are after with the GROUP_CONCAT function

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array(
                     (select GROUP_CONCAT(
                                 json_object('id',id,'parent_id',parent_id,'desc',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

这几乎有效，它最终将子查询视为一个字符串，将转义字符留在那里.

This almost works, it ends up treating the subquery as a string which leaves the escape characters in there.

'{\"id\": 1, 
\"desc\": \"parent row 1\", 
\"child_objects\": 
    [\"
    {\\\"id\\\": 1,
     \\\"desc\\\": \\\"child row 1\\\", 
    \\\"parent_id\\\": 1
    },
    {\\\"id\\\": 2, 
    \\\"desc\\\": \\\"child row 2\\\", 
    \\\"parent_id\\\": 1}\"
    ]
}'

为了使其以适当的格式工作，您需要更改创建 JSON 输出的方式，如下所示:

In order to get this working in an appropriate format, you need to change the way you create the JSON output as follows:

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',(select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    'id',id,'parent_id',parent_id,'desc',`desc`)),
                ']')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

这将为您提供所需的确切结果:

This will give you the exact result you require:

'{\"id\": 1, 
\"desc\": \"parent row 1\", 
\"child_objects\": 
    [{\"id\": 1, 
    \"desc\": \"child row 1\", 
    \"parent_id\": 1
    }, 
    {\"id\": 2, 
    \"desc\": \"child row 2\", 
    \"parent_id\": 1
    }]  
}'

相关文章