JSON_ARRAYAGG

描述

该函数将结果集聚合为单个 JSON 数组，其元素由行组成。此数组中元素的顺序未定义。

该函数作用于计算为单个值的列或表达式。

语法

JSON_ARRAYAGG(col_or_expr)[over_clause]

说明

参数 col_or_expr 为列或表达式。如果结果不包含行，或者出现错误，则返回 NULL。

提示

该函数暂不支持窗口函数，即 OVER 子句。

示例

CREATE TABLE tbl1 (oid INT, attr VARCHAR(100), value VARCHAR(100));
Query OK, 0 rows affected (0.000 sec)

INSERT INTO tbl1 VALUES (2, 'color', 'red'),(2, 'fabric', 'silk'),(3,'color','green'),(3,'shape','square');
Query OK, 4 rows affected (0.000 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------+
| oid  | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.001 sec)

INSERT INTO tbl1 SELECT * FROM tbl1;
Query OK, 4 rows affected (0.000 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+----------------------------------------+
| oid  | attributes                             |
+------+----------------------------------------+
|    2 | ["color", "fabric", "color", "fabric"] |
|    3 | ["color", "shape", "color", "shape"]   |
+------+----------------------------------------+
2 rows in set (0.001 sec)