集約関数の活用(JSON_ARRAYAGG, JSON_OBJECTAGG)

2022/11/13

集約関数である「JSON_ARRAYAGG」「JSON_OBJECTAGG」の使い方を確認します。

動作確認テーブル

以下テーブルで動作確認します。

mysql> SELECT * FROM test;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | aaa  |    76 |
|  2 | bbb  |  NULL |
|  3 | ccc  |    98 |
|  4 | ddd  |    43 |
|  5 | eee  |    64 |
|  6 | fff  |    54 |
|  7 | ggg  |  NULL |
+----+------+-------+
7 rows in set (0.00 sec)

JSON_ARRAYAGGの活用

JSON_ARRAYAGG関数を利用すると、JSONリストの形に集約できます。

mysql> SELECT 
    ->   JSON_ARRAYAGG(name)
    -> FROM test;
+---------------------------------------------------+
| JSON_ARRAYAGG(name)                               |
+---------------------------------------------------+
| ["aaa", "bbb", "ccc", "ddd", "eee", "fff", "ggg"] |
+---------------------------------------------------+
1 row in set (0.01 sec)

JSON_OBJECTAGGの活用

JSON_OBJECTAGG関数を利用すると、JSONオブジェクトの形に集約できます。

第1引数にkeyを指定して、第2引数にvalueを指定します。

mysql> SELECT 
    ->   JSON_OBJECTAGG(id, name)
    -> FROM test;
+--------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id, name)                                                             |
+--------------------------------------------------------------------------------------+
| {"1": "aaa", "2": "bbb", "3": "ccc", "4": "ddd", "5": "eee", "6": "fff", "7": "ggg"} |
+--------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

以下のように、JSON_OBJECTと合わせて利用すると、複数カラムをまとめて表示することができます。

mysql> SELECT
    ->   JSON_PRETTY(
    ->     JSON_OBJECTAGG(
    ->       id, 
    ->       JSON_OBJECT('name', name, 'score', score)
    ->     )
    ->   ) AS reslut  
    -> FROM test;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reslut                                                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "1": {
    "name": "aaa",
    "score": 76
  },
  "2": {
    "name": "bbb",
    "score": null
  },
  "3": {
    "name": "ccc",
    "score": 98
  },
  "4": {
    "name": "ddd",
    "score": 43
  },
  "5": {
    "name": "eee",
    "score": 64
  },
  "6": {
    "name": "fff",
    "score": 54
  },
  "7": {
    "name": "ggg",
    "score": null
  }
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)