Bug #78117 JSON aggregate array function

• Submitted: 18 Aug 2015 2:32
• Modified: 31 Aug 2016 7:45
• Reporter: Jesper wisborg Krogh
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[18 Aug 2015 2:32] Jesper wisborg Krogh

Description:
5.7.8 added native support for JSON however there are currently no JSON functions that can be used for aggregate queries, for example to create a JSON array from several rows in a table.

How to repeat:
For example consider the world sample database and assume you want a JSON object with an array for each country and that array has each city for that country with the population, so:

{
  "CountryCode": "AUS",
  "Cities": [
    {
      "Name": "Sydney",
      "Population": 3276207
    },
    {
      "Name": "Melbourne",
      "Population": 2865329
    },
    ...
  ]
},
...

So that would be a query similar to:

SELECT JSON_OBJECT(
          "CountryCode", co.Code,
          "Cities", JSON_ARRAY_AGGREGATE(
                       JSON_OBJECT(
                          "Name", ci.Name,
                          "Population", ci.Population
                       )
                    )
       ) AS Country
  FROM world.Country co
       INNER JOIN world.City ci ON ci.CountryCode = co.Code
 WHERE co.Continent = 'Oceania'
 GROUP BY co.Code;

[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.