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"
        }
    ]
}]

エラー

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 中国語 Web サイトの他の関連記事を参照してください。