Purpose
This function aggregates the result set into a single JSON array, with each element representing 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]
Considerations
The col_or_expr parameter specifies a column or expression. If no rows are returned or an error occurs, NULL is returned.
Notice
This function does not support window functions, such as the OVER clause.
Examples
obclient> CREATE TABLE tbl1 (oid INT, attr VARCHAR(100), value VARCHAR(100));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (2, 'color', 'red'),(2, 'fabric', 'silk'),(3,'color','green'),(3,'shape','square');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------+
| oid | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set
obclient> INSERT INTO tbl1 SELECT * FROM tbl1;
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> 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