JSON 値を集約する JSON 関数

このページにリストされている関数は、TiDB がサポートする集計関数の一部ですが、JSON の操作に特化しています。

JSON_ARRAYAGG()

JSON_ARRAYAGG(key)関数は、指定されたkeyに従ってキーの値を JSON 配列に集約します。 keyは通常、式または列名です。

例：

ここでは、テーブルの 1 つの列にある 2 つの行が JSON 配列に集約されます。

SELECT JSON_ARRAYAGG(v) FROM (SELECT 1 'v' UNION SELECT 2);

+------------------+
| JSON_ARRAYAGG(v) |
+------------------+
| [2, 1]           |
+------------------+
1 row in set (0.00 sec)

JSON_OBJECTAG() は、

JSON_OBJECTAGG(key,value)関数は、指定されたkeyとvalueに従って、キーとキーの値を JSON オブジェクトに集約します。 keyとvalueどちらも通常、式または列名です。

例：

まず、2 つのテーブルを作成し、そこにいくつかの行を追加します。

CREATE TABLE plants (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE plant_attributes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plant_id INT, attribute VARCHAR(255),
    value VARCHAR(255),
    FOREIGN KEY (plant_id) REFERENCES plants(id)
);

INSERT INTO plants
VALUES
(1,"rose"),
(2,"tulip"),
(3,"orchid");

INSERT INTO plant_attributes(plant_id,attribute,value)
VALUES
(1,"color","red"),
(1,"thorns","yes"),
(2,"color","orange"),
(2,"thorns","no"),
(2,"grows_from","bulb"),
(3,"color","white"),
(3, "thorns","no");

これで、作成されたテーブルがどのように見えるかを確認できます。

TABLE plants;

+----+--------+
| id | name   |
+----+--------+
|  1 | rose   |
|  2 | tulip  |
|  3 | orchid |
+----+--------+
3 rows in set (0.00 sec)

TABLE plant_attributes;

+----+----------+------------+--------+
| id | plant_id | attribute  | value  |
+----+----------+------------+--------+
|  1 |        1 | color      | red    |
|  2 |        1 | thorns     | yes    |
|  3 |        2 | color      | orange |
|  4 |        2 | thorns     | no     |
|  5 |        2 | grows_from | bulb   |
|  6 |        3 | color      | white  |
|  7 |        3 | thorns     | no     |
+----+----------+------------+--------+
7 rows in set (0.00 sec)

このデータにはJSON_OBJECTAGG()関数を使用できます。ここでは、グループごとに複数のキー/値のペアが JSON オブジェクトに集約されていることがわかります。

SELECT
    p.name,
    JSON_OBJECTAGG(attribute,value)
FROM
    plant_attributes pa
    LEFT JOIN plants p ON pa.plant_id=p.id
GROUP BY
    plant_id;

+--------+-----------------------------------------------------------+
| name   | JSON_OBJECTAGG(attribute,value)                           |
+--------+-----------------------------------------------------------+
| rose   | {"color": "red", "thorns": "yes"}                         |
| orchid | {"color": "white", "thorns": "no"}                        |
| tulip  | {"color": "orange", "grows_from": "bulb", "thorns": "no"} |
+--------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

参照

• JSON 関数の概要
• JSON データ型