Purpose
This function aggregates the results into a single JSON array, where each element is a row. The order of the elements in this array is undefined.
This function is applied to a column or expression that evaluates to a single value.
Syntax
JSON_ARRAYAGG(col_or_expr)[over_clause]
Description
The col_or_expr parameter is a column or an expression. If the result contains no rows or an error occurs, NULL is returned.
Notice
This function does not support window functions, that is, 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