MySQL でクエリー結果から JSON を生成する

ここでは、MySQL のクエリーの結果から、JSON を生成する方法をご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちらのスクリプトを実行して school_db データベースを作成しておいてください。

MySQL でクエリー結果から JSON を生成する
MySQL でクエリー結果からネストした JSON を生成する

MySQL でクエリー結果から JSON を生成する

MySQL には JSON を生成するための組み込み関数が用意されています。

今回はクエリー結果から JSON を生成するのに、JSON_OBJECT() と JSON_ARRAYAGG() 関数を使います。

JSON_OBJECT() 関数は次のように引数にキーと値の対を指定しすると、キーバリューペアの JSON オブジェクトを返す関数です。

JSON_OBJECT([key1, value1[, key2, value2] ...])

キーと値のセットを二個指定すると、{"key1": "value1", "key2": "value2"} のような JSON オブジェクトが返ります。

キーが NULL だったり、引数の数が偶数でないとエラーになります。

キーと値のセットの順番は、引数として渡した順番と変わる可能性があります。

同じキーを複数回指定してしまった場合は、最後に指定したキーと値のセットのみが返ります。

JSON_ARRAYAGG() 関数は結果セットをひとつの JSON 配列にして返す集計関数です。

JSON_ARRAYAGG(col_or_expr) [over_clause]

今回は、JSON_ARRAYAGG() に JSON_OBJECT() で生成した JSON オブジェクトを渡して、複数の JSON オブジェクトをひとつの JSON につなげます。

それでは、実際にクエリーの結果セットから JSON_OBJECT() と JSON_ARRAYAGG() 関数を使って JSON を生成してみます。

次のような students テーブルがあります。

この students テーブルの、student_number、first_name、last_name を含む JSON を生成するには次のようにできます。

SELECT  JSON_ARRAYAGG(
			JSON_OBJECT(
				'studentNumber', student_number, 
				'firstName', first_name,
                'lastName', last_name
			)
		) AS student_json
FROM 	students;

SELECT 文で JSON_OBJECT() に、キーの名前と対応するカラムのセットを三つ続けて渡しています。

それの戻り値を JSON_ARRAYAGG() に渡して、JSON をつなげてひとつの JSON オブジェクトにしています。

このクエリーを実行すると、次のような結果が得られます。

[{"lastName": "Tanaka", "firstName": "Yuta", "studentNumber": "S000001"}, {"lastName": "Hata", "firstName": "Sakura", "studentNumber": "S000002"}, {"lastName": "Tanaka", "firstName": "Aya", "studentNumber": "S000003"}, {"lastName": "Suzuki", "firstName": "Hiroki", "studentNumber": "S000004"}, {"lastName": "Sasaki", "firstName": "Yuri", "studentNumber": "S000005"}, {"lastName": "Saito", "firstName": "Ryo", "studentNumber": "S000006"}]

[実行結果]

見やすいようにフォーマットすると次のようになっています。

[
   {
      "lastName":"Tanaka",
      "firstName":"Yuta",
      "studentNumber":"S000001"
   },
   {
      "lastName":"Hata",
      "firstName":"Sakura",
      "studentNumber":"S000002"
   },
   {
      "lastName":"Tanaka",
      "firstName":"Aya",
      "studentNumber":"S000003"
   },
   {
      "lastName":"Suzuki",
      "firstName":"Hiroki",
      "studentNumber":"S000004"
   },
   {
      "lastName":"Sasaki",
      "firstName":"Yuri",
      "studentNumber":"S000005"
   },
   {
      "lastName":"Saito",
      "firstName":"Ryo",
      "studentNumber":"S000006"
   }
]

MySQL でクエリー結果からネストした JSON を生成する

次は MySQL の JSON_OBJECT() と JSON_ARRAYAGG() 関数を使って、ネストした JSON を生成してみます。

students テーブルのほかに、次のような exams テーブルと exam_results テーブルがあります。

students テーブルの、student_number、first_name、last_name に加えて、その学生のテスト結果として、exams テーブルから exam_name_en カラムの値と、exam_results テーブルから score カラムの値を、examResults 要素として JSON に配列で追加するには次のようにできます。

SELECT	JSON_ARRAYAGG(
			JSON_OBJECT(
			'studentNumber', s.student_number,
			'firstName', s.first_name,
			'lastName', last_name,
			'examResults', (SELECT	JSON_ARRAYAGG(
										JSON_OBJECT(
											'examName', e.exam_name_en,
											'score', er.score
										)
									)   
							FROM 	exam_results AS er
										INNER JOIN exams AS e
											ON er.exam_id = e.exam_id
							WHERE  	er.student_id = s.student_id)
			)
		) AS student_json
 FROM	students AS s;

6 ~ 15 行目が新しく追加された箇所で、examResults というキーに対して、値に JSON_ARRAYAGG() と JSON_OBJECT() を使って、exam_results と exams テーブルを JOIN した結果セットから JSON を生成したものを指定しています。

このクエリーを実行すると、次のような結果が得られます。

[{"lastName": "Tanaka", "firstName": "Yuta", "examResults": [{"score": 85, "examName": "Math 1"}, {"score": 77, "examName": "English 1"}], "studentNumber": "S000001"}, {"lastName": "Hata", "firstName": "Sakura", "examResults": [{"score": 60, "examName": "Math 1"}], "studentNumber": "S000002"}, {"lastName": "Tanaka", "firstName": "Aya", "examResults": [{"score": 92, "examName": "English 1"}], "studentNumber": "S000003"}, {"lastName": "Suzuki", "firstName": "Hiroki", "examResults": [{"score": 98, "examName": "Math 1"}, {"score": 81, "examName": "English 1"}], "studentNumber": "S000004"}, {"lastName": "Sasaki", "firstName": "Yuri", "examResults": [{"score": 73, "examName": "Math 1"}], "studentNumber": "S000005"}, {"lastName": "Saito", "firstName": "Ryo", "examResults": null, "studentNumber": "S000006"}]

[実行結果]

見やすいようにフォーマットすると次のようになっています。

[
   {
      "lastName":"Tanaka",
      "firstName":"Yuta",
      "examResults":[
         {
            "score":85,
            "examName":"Math 1"
         },
         {
            "score":77,
            "examName":"English 1"
         }
      ],
      "studentNumber":"S000001"
   },
   {
      "lastName":"Hata",
      "firstName":"Sakura",
      "examResults":[
         {
            "score":60,
            "examName":"Math 1"
         }
      ],
      "studentNumber":"S000002"
   },
   {
      "lastName":"Tanaka",
      "firstName":"Aya",
      "examResults":[
         {
            "score":92,
            "examName":"English 1"
         }
      ],
      "studentNumber":"S000003"
   },
   {
      "lastName":"Suzuki",
      "firstName":"Hiroki",
      "examResults":[
         {
            "score":98,
            "examName":"Math 1"
         },
         {
            "score":81,
            "examName":"English 1"
         }
      ],
      "studentNumber":"S000004"
   },
   {
      "lastName":"Sasaki",
      "firstName":"Yuri",
      "examResults":[
         {
            "score":73,
            "examName":"Math 1"
         }
      ],
      "studentNumber":"S000005"
   },
   {
      "lastName":"Saito",
      "firstName":"Ryo",
      "examResults":null,
      "studentNumber":"S000006"
   }
]

以上、MySQL のクエリーの結果から、JSON を生成する方法をご説明しました。