如何從 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中文網其他相關文章！