JSON_OBJECTAGG() – Create a JSON Object from Query Results in MySQL

MySQL includes an aggregate function called JSON_OBJECTAGG(). This function enables you to create a JSON object containing key-value pairs. More specifically, it lets you create this JSON object based on the results of a query.

It accepts two arguments, the first of these being used as a key and the second as a value. These arguments can be column names or expressions.

Syntax

The syntax goes like this:

JSON_OBJECTAGG(key, value)

Where key is the column or expression that represents the key of the key/value pair, and value is the column or expression that represents the value of the key/value pair.

Example

Here’s an example to demonstrate.

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

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

Result:

+-----------------+---------------+------------+
| State           | City          | Population |
+-----------------+---------------+------------+
| Capital Region  | Canberra      |     322723 |
| New South Wales | Sydney        |    3276207 |
| New South Wales | Newcastle     |     270324 |
| New South Wales | Central Coast |     227657 |
| New South Wales | Wollongong    |     219761 |
| Queensland      | Brisbane      |    1291117 |
| Queensland      | Gold Coast    |     311932 |
| Queensland      | Townsville    |     109914 |
| Queensland      | Cairns        |      92273 |
| South Australia | Adelaide      |     978100 |
| Tasmania        | Hobart        |     126118 |
| Victoria        | Melbourne     |    2865329 |
| Victoria        | Geelong       |     125382 |
| West Australia  | Perth         |    1096829 |
+-----------------+---------------+------------+

We can adjust that query, so that the Name column (in this example we’ve given this column an alias of City) becomes a key, and the Population column becomes a value.

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_OBJECTAGG(Name, Population) AS 'City/Population'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY State;

Result:

+-----------------+-----------------------------------------------------------------------------------------+
| State           | City/Population                                                                         |
+-----------------+-----------------------------------------------------------------------------------------+
| Capital Region  | {"Canberra": 322723}                                                                    |
| New South Wales | {"Sydney": 3276207, "Newcastle": 270324, "Wollongong": 219761, "Central Coast": 227657} |
| Queensland      | {"Cairns": 92273, "Brisbane": 1291117, "Gold Coast": 311932, "Townsville": 109914}      |
| South Australia | {"Adelaide": 978100}                                                                    |
| Tasmania        | {"Hobart": 126118}                                                                      |
| Victoria        | {"Geelong": 125382, "Melbourne": 2865329}                                               |
| West Australia  | {"Perth": 1096829}                                                                      |
+-----------------+-----------------------------------------------------------------------------------------+

In this case we grouped the cities by their state/district. However, if we just want one big JSON object containing all cities/populations for that country, we can remove the state/district (and its associated GROUP BY clause) from the query altogether.

SELECT
  JSON_OBJECTAGG(Name, Population) AS 'City/Population'
FROM City
WHERE CountryCode = 'AUS';

Result:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| City/Population                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"Perth": 1096829, "Cairns": 92273, "Hobart": 126118, "Sydney": 3276207, "Geelong": 125382, "Adelaide": 978100, "Brisbane": 1291117, "Canberra": 322723, "Melbourne": 2865329, "Newcastle": 270324, "Gold Coast": 311932, "Townsville": 109914, "Wollongong": 219761, "Central Coast": 227657} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Also see the JSON_ARRAYAGG() function which allows you to aggregate your query results into a JSON array.