如何从 MySQL 中的一对多关系生成嵌套 JSON 对象？

使用 MySQL 原生 JSON 函数生成嵌套 JSON 对象

本文演示了如何利用 MySQL 版本 5.7.12 的原生 JSON 函数来构造嵌套JSON 文档表示关系中的一对多关系data.

问题陈述

给定两个表，parent_table 和 child_table，表示父子关系，目标是生成具有以下内容的 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"
            }
        ]
    }
]

解决方案

最初，尝试使用简单嵌套子查询创建嵌套 JSON 对象会导致错误，因为 MySQL 期望父级中存在简单对象对JSON 对象。为了解决这个问题，我们利用 GROUP_CONCAT 函数将多个子查询结果连接成一个字符串，然后将该结果转换为 JSON 数组。

但是，由于子查询的处理方式，结果 JSON 输出包含转义字符一个字符串。为了解决这个问题，我们使用 CAST 和 CONCAT 函数直接在子查询中创建 JSON 数组，而不是依赖主查询中的串联。

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;

此查询产生所需的输出：

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

以上是如何从 MySQL 中的一对多关系生成嵌套 JSON 对象？的详细内容。更多信息请关注PHP中文网其他相关文章！