Purpose
This function takes two column names or expressions as parameters, the first as the key and the second as the value, and returns a JSON object that contains the key-value pairs.
Syntax
JSON_OBJECTAGG(key, value) [over_clause]
Purpose
The key, value pair represents a key-value pair. It returns NULL if no rows are returned or an error occurs. An error occurs if any key is NULL or the number of arguments is not 2.
In JSON, an object cannot contain duplicate key-value pairs, so duplicate key-value pairs are deleted. The result of an aggregation operation mechanism The result set is processed row by row, so the value of the last duplicate key is preserved. The SELECT statement The order in which the returned rows appear may affect which value is retained.
Note
Support for window functions (that is, the OVER clause) is not available in this function.
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