Purpose
JSON_OBJECTAGG() takes column names or expressions as arguments, with the first one as the key and the second one as the value, and returns a JSON object containing a key-value pair.
Syntax
JSON_OBJECTAGG(key, value) [over_clause]
Description
key, value specifies a key-value pair. If the results do not contain rows or an error occurs, NULL is returned. If any key name is NULL or the number of arguments is not 2, an error occurs.
The JSON data type specification does not allow duplicate keys in JSON objects. Therefore, values with duplicate keys will be discarded. The aggregate operation mechanism processes a result set row by row. Therefore, the last value of the duplicate key is retained. However, the SELECT statement returns data rows in an unordered way. The value to be retained depends on the order in which the data is returned.
Notice
This function does not support analytic functions. Therefore, the OVER clause is not supported.
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