JSON_ARRAYAGG

Description

This function aggregates the result set into a single JSON array, where each element represents a row. The order of elements in the array is undefined.

This function operates on a column or expression that evaluates to a single value.

Syntax

JSON_ARRAYAGG(col_or_expr)[over_clause]

Description

The col_or_expr parameter specifies the column or expression. If no rows are returned or an error occurs, the function returns NULL.

tip

This function does not support window functions, such as the OVER clause.

Examples

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)