如何使用 JSON_OBJECT 和 JSON_ARRAY 從 MySQL 關係資料產生巢狀 JSON 物件？

使用 MySQL 原生 JSON 函數產生巢狀 JSON 物件

MySQL 版本 5.7.12 引入了原生 JSON 函數，可以直接從關聯式資料建立 JSON 文件。為了實現這一目標，可以利用 JSON_OBJECT() 和 JSON_ARRAY() 函數。

考慮提供的範例表：

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

目標是產生包含巢狀物件的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"
        }
    ]
}]

解決錯誤

先前的嘗試遇到了錯誤“子查詢返回超過1 行”，因為JSON 物件需要簡單的鍵值對作為輸入。傳回多行的子查詢需要轉換為單一字串或 JSON 陣列。

解決方案

要克服此錯誤，可以使用 MySQL 的 GROUP_CONCAT() 函數。以下查詢完成此操作：

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;

為了消除輸出中的轉義字符，查詢被修改為使用CAST() 和CONCAT() 函數：

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;

此改進的查詢產生所需的JSON 輸出：

'{\"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_OBJECT 和 JSON_ARRAY 從 MySQL 關係資料產生巢狀 JSON 物件？的詳細內容。更多資訊請關注PHP中文網其他相關文章！