Bug #78117 | JSON aggregate array function | ||
---|---|---|---|
Submitted: | 18 Aug 2015 2:32 | Modified: | 31 Aug 2016 7:45 |
Reporter: | Jesper wisborg Krogh | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: JSON | Severity: | S4 (Feature request) |
Version: | 5.7.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[18 Aug 2015 2:32]
Jesper wisborg Krogh
[18 Aug 2015 2:34]
Jesper wisborg Krogh
Posted by developer: Current workaround is to manually create the JSON object using GROUP_CONCAT.
[31 Aug 2016 7:45]
Jon Stephens
Documented fix in the MySQL 8.0.1 changelog, as follows: Added two JSON aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). The JSON_ARRAYAGG() function takes a column or column expression as an argument, and aggregates the result set as a single JSON array, as shown here: mysql> SELECT col FROM t1; +--------------------------------------+ | col | +--------------------------------------+ | {"key1": "value1", "key2": "value2"} | | {"keyA": "valueA", "keyB": "valueB"} | +--------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT JSON_ARRAYAGG(col) FROM t1; +------------------------------------------------------------------------------+ | JSON_ARRAYAGG(col) | +------------------------------------------------------------------------------+ | [{"key1": "value1", "key2": "value2"}, {"keyA": "valueA", "keyB": "valueB"}] | +------------------------------------------------------------------------------+ 1 row in set (0.00 sec) The order of the array elements is unspecified. JSON_OBJECTAGG() takes two columns or expressions which it interprets as a key and a value, respectively; it returns the result as a single JSON object, as shown here: mysql> SELECT id, col FROM t1; +------+--------------------------------------+ | id | col | +------+--------------------------------------+ | 1 | {"key1": "value1", "key2": "value2"} | | 2 | {"keyA": "valueA", "keyB": "valueB"} | +------+--------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT JSON_OBJECTAGG(id, col) FROM t1; +----------------------------------------------------------------------------------------+ | JSON_OBJECTAGG(id, col) | +----------------------------------------------------------------------------------------+ | {"1": {"key1": "value1", "key2": "value2"}, "2": {"keyA": "valueA", "keyB": "valueB"}} | +----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) A NULL key causes an error; duplicate keys are ignored. For more information, see Functions That Create JSON Values. (Bug #78117, Bug #21647417) Closed.