Purpose
JSON_ARRAYAGG() aggregates result sets into a JSON array that consists of rows. The order of elements in this array is not defined.
This function applies to a column or expression that is calculated into a single value.
Syntax
JSON_ARRAYAGG(col_or_expr)[over_clause]
Description
The value of col_or_expr is a column or expression. If the results do not contain rows or an error occurs, NULL is returned.
Notice
This function does not support analytic functions. Therefore, the OVER clause is not supported.
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