MySQL の GROUP_CONCAT の応用例（検索結果を Json array型にする）

こんにちは。
MySQL で、検索結果を Json の array型で得る方法を考えてみました。GROUP_CONCAT()¹の応用でaggregation 処理に当たります。

mysql> SELECT JSON_TYPE(CAST(CONCAT('[', GROUP_CONCAT(my_id), ']') AS JSON)) as json_array FROM my_table;
+--------------+
| json_array   |
|--------------|
| ARRAY        |
+--------------+

なお、GROUP_CONCAT()の出力自身は文字列です²。下記のような検索実行もできます。

mysql> CREATE TABLE my_table (id integer not null);
mysql> INSERT INTO my_table (id) VALUES (1), (2), (3), (4);
mysql> SELECT id_excluded as id, (SELECT GROUP_CONCAT(id) FROM my_table WHERE id != id_excluded) as other_ids
  from (SELECT id as id_excluded from my_table) tab_dummy;
+------+-----------+
|   id | other_ids |
|------+-----------|
|    1 | 2,3,4     |
|    2 | 1,3,4     |
|    3 | 1,2,4     |
|    4 | 1,2,3     |
+------+-----------+

Postgresql では、ARRAY型を出力する array_agg()³ という関数が用意されているので、上記のような手間は不要ですね。

---

---

1. こちらにも紹介がありました：「MYSQLの関数 GROUP_CONCAT」 ↩

2. 出力文字数の上限（group_concat_max_len 、標準値 1024）を超えた場合にはエラーが出ます（例 Row 99 was cut by GROUP_CONCAT()）。 ↩

3. こちらにも紹介がありました：「PostgreSQLのあまり知られていない型3種」 ↩