Description
This function takes two column names or expressions as parameters, uses 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 the result contains no rows or an error occurs, NULL is returned. An error occurs if any key name is NULL or if the number of parameters does not equal 2.
According to the requirements of JSON text, an object cannot contain duplicate key-value pairs, so duplicate key-value pairs will be removed. Due to the aggregation operation mechanism The result set is processed row by row, so the value of the last duplicate key is retained, whereas the SELECT statement The returned data rows may be unordered, so which value is retained last depends on the order in which the data is returned.
Notice
This function does not support window functions, that is, the OVER clause.
Examples
obclient> 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
obclient> CREATE TABLE tbl2(c VARCHAR(10), i INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl2 VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT c, i FROM tbl2;
+------+------+
| c | i |
+------+------+
| key | 3 |
| key | 4 |
| key | 5 |
+------+------+
3 rows in set
obclient> SELECT JSON_OBJECTAGG(c, i) FROM tbl2;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5} |
+----------------------+
1 row in set