MySQLのGROUP_CONCAT関数でJSON形式のデータを作成する方法

MySQLでは、GROUP_CONCAT() 関数と CONCAT() 関数を組み合わせることで、複数行のレコードをJSON形式の文字列としてまとめて取得できます。この記事では、その具体的な手順をサンプルコード付きで解説します。

基本構文

GROUP_CONCAT() を使ってJSON形式を作成する基本的な構文は以下の通りです。

SELECT yourColumnName1,
   GROUP_CONCAT(CONCAT('{anyName:"', yourColumnName, '", anyName:"', yourColumnName2, '"}')) AS anyVariableName
FROM yourTableName
GROUP BY yourColumnName1;

サンプルテーブルの作成

まず、動作確認用のテーブルを作成しましょう。以下のクエリを実行します。

mysql> CREATE TABLE JsonFormatDemo (
    -> UserId INT,
    -> UserName VARCHAR(100),
    -> UserEmail VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.99 sec)

テストデータの挿入

次に、INSERT文を使ってサンプルデータを登録します。

mysql> INSERT INTO JsonFormatDemo VALUES(101,'John','John@gmail.com');
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(101,'Bob','John@gmail.com');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(102,'Carol','Carol@gmail.com');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(103,'Sam','Sam@gmail.com');
Query OK, 1 row affected (0.15 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> SELECT * FROM JsonFormatDemo;

出力結果

+--------+----------+-----------------+
| UserId | UserName | UserEmail       |
+--------+----------+-----------------+
|    101 | John     | John@gmail.com  |
|    101 | Bob      | John@gmail.com  |
|    102 | Carol    | Carol@gmail.com |
|    103 | Sam      | Sam@gmail.com   |
+--------+----------+-----------------+
4 rows in set (0.00 sec)

GROUP_CONCAT()でJSON形式に変換する

ここからが本題です。GROUP_CONCAT() 内で CONCAT() を使い、各レコードを「{キー:値}」の形式の文字列に整形します。UserIdごとにグループ化することで、同一ユーザーの複数レコードがひとつのJSON風文字列にまとまります。

mysql> SELECT UserId,
    -> GROUP_CONCAT(CONCAT('{Name:"', UserName, '", Email:"', UserEmail, '"}')) AS JsonFormat
    -> FROM JsonFormatDemo
    -> GROUP BY UserId;

出力結果

+--------+----------------------------------------------------------------------------+
| UserId | JsonFormat                                                                 |
+--------+----------------------------------------------------------------------------+
|    101 | {Name:"John", Email:"John@gmail.com"},{Name:"Bob", Email:"John@gmail.com"} |
|    102 | {Name:"Carol", Email:"Carol@gmail.com"}                                    |
|    103 | {Name:"Sam", Email:"Sam@gmail.com"}                                        |
+--------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

補足：より厳密なJSONを出力するには

上記の方法はあくまで「JSON形式に似た文字列」を生成するテクニックです。より厳密なJSONが必要な場合は、MySQL 5.7以降で利用可能な JSON_OBJECT() や JSON_ARRAYAGG() 関数を使うと、エスケープ処理なども自動的に行われ、正しいJSONを簡単に生成できます。また、GROUP_CONCATにはデフォルトで最大長（group_concat_max_len、初期値1024バイト）の制限があるため、大量のデータを扱う場合はこの設定値を引き上げる必要がある点にも注意してください。