Purpose
You can call this function to aggregate 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]
Notes
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
OVERclause 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