Топ-100
 

JSON_OBJECTAGG

База данных: MySQL
Текст

Syntax:
JSON_OBJECTAGG(key, value) [over_clause]

Takes two column names or expressions as arguments, the first of these
being used as a key and the second as a value, and returns a JSON
object containing key-value pairs. Returns NULL if the result contains
no rows, or in the event of an error. An error occurs if any key name
is NULL or the number of arguments is not equal to 2.

As of MySQL 8.0.14, this function executes as a window function if
over_clause is present. over_clause is as described in
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

Пример

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)