JSON_OBJECTAGG

Description

The function uses two column names or expressions as arguments, the first as the key and the second as the value, and returns a JSON object containing key-value pairs.

Syntax

JSON_OBJECTAGG(key, value) [over_clause]

Description

key, value indicates a key-value pair. If no rows are returned or an error occurs, the return value is NULL. An error occurs if a key name is NULL or the parameter count does not equal 2.

Because JSON text cannot contain duplicate key-value pairs, the JSON text must delete duplicate keys. Aggregation operations are performed. The result set is processed line by line, so the value of the last duplicate key is retained. The returned data rows may be in an unpredictable order, so which value is retained depends on the order of the returned data.

tip

The function currently does not support window functions, i.e. the OVER clause.

Example

SELECT oid, JSON_OBJECTAGG(attr, value) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------------------------+
| oid  | attributes                            |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.001 sec)

CREATE TABLE tbl2(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.000 sec)

 INSERT INTO tbl2 VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.000 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT c, i FROM tbl2;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.001 sec)

SELECT JSON_OBJECTAGG(c, i) FROM tbl2;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.001 sec)