MySQL で 1 対多の関係からネストされた JSON オブジェクトを生成するにはどうすればよいですか?

MySQL ネイティブ JSON 関数を使用したネストされた JSON オブジェクトの生成

この記事では、MySQL バージョン 5.7.12 のネイティブ JSON 関数を利用してネストされた JSON 関数を構築する方法を説明します。リレーショナルにおける 1 対多の関係を表す JSON ドキュメントdata.

問題ステートメント

親子関係を表す 2 つのテーブル、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 関数を使用して複数のサブクエリの結果を 1 つの文字列に連結し、その結果を 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 で 1 対多の関係からネストされた JSON オブジェクトを生成するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。