Among the many MySQL aggregate functions is one called JSON_ARRAYAGG(). This function enables you to aggregate a result set as a single JSON array. Each row of the result set ends up as a single element in the array.

The order of the elements in the array is undefined.

Syntax

The syntax goes like this:

JSON_ARRAYAGG(col_or_expr)

Where col_or_expr is a column or an expression that evaluates to a single value.

Example

Here’s an example to demonstrate.

Here’s a common query we might run without the JSON_ARRAYAGG() function:

SELECT 
  District AS 'State',
  Name AS 'City'
FROM City
WHERE CountryCode = 'AUS'
ORDER BY State;

Result:

+-----------------+---------------+
| State           | City          |
+-----------------+---------------+
| Capital Region  | Canberra      |
| New South Wales | Sydney        |
| New South Wales | Newcastle     |
| New South Wales | Central Coast |
| New South Wales | Wollongong    |
| Queensland      | Brisbane      |
| Queensland      | Gold Coast    |
| Queensland      | Townsville    |
| Queensland      | Cairns        |
| South Australia | Adelaide      |
| Tasmania        | Hobart        |
| Victoria        | Melbourne     |
| Victoria        | Geelong       |
| West Australia  | Perth         |
+-----------------+---------------+

We can adjust that query, so that each city becomes an element in an array. To do this, we simply pass the Name column (the name of the city) to the JSON_ARRAYAGG() function.

We also use a GROUP BY clause to group the results by the District column (in this case, we’ve created an alias for this column called State).

SELECT 
  District AS 'State',
  JSON_ARRAYAGG(Name) AS 'Cities'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY State;

Result:

+-----------------+--------------------------------------------------------+
| State           | Cities                                                 |
+-----------------+--------------------------------------------------------+
| Capital Region  | ["Canberra"]                                           |
| New South Wales | ["Sydney", "Newcastle", "Central Coast", "Wollongong"] |
| Queensland      | ["Brisbane", "Gold Coast", "Townsville", "Cairns"]     |
| South Australia | ["Adelaide"]                                           |
| Tasmania        | ["Hobart"]                                             |
| Victoria        | ["Melbourne", "Geelong"]                               |
| West Australia  | ["Perth"]                                              |
+-----------------+--------------------------------------------------------+

Also see the JSON_OBJECTAGG() function which allows you to create a JSON object from a query.