7 Functions that Create JSON Values in MySQL

Posted on by Ian

MySQL includes some inbuilt functions that can be used to create JSON values, such as JSON arrays, JSON objects, or a quoted string literal.

Here are seven functions that allow us to create JSON values in MySQL.

The JSON_ARRAY() Function

The JSON_ARRAY() function creates a JSON array from a list of values. We provide the values, and the function returns a JSON array containing those values.

Example:

SELECT JSON_ARRAY(1, 'Homer', 3);

Result:

[1, "Homer", 3]

As demonstrated, we can pass different data types and the function will deal with it accordingly. In the above example we passed two numbers and a string. JSON_ARRAY() took the string and enclosed it in double quotes.

The JSON_OBJECT() Function

The JSON_OBJECT() function creates a JSON object from a list of key-value pairs. We provide the key-value pairs, and the function returns a JSON object containing them.

Example:

SELECT JSON_OBJECT('City', 'Cairns', 'Population', 139693);

Result:

{"City": "Cairns", "Population": 139693}

The JSON_QUOTE() Function

The JSON_QUOTE() function quotes a string as a JSON value by wrapping it with double quote characters and escaping interior quote and other characters, then returning the result as a utf8mb4 string.

Example:

SELECT JSON_QUOTE('Homer said "DOH!"');

Result:

"Homer said \"DOH!\""

The returned value can be used as a JSON string literal in a JSON document.

The JSON_ARRAYAGG() Function

The JSON_ARRAYAGG() function allows us 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.

Suppose we have the following table:

SELECT * FROM Pets;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     3 |         2 |       2 | Scratch | 2018-10-01 |
+-------+-----------+---------+---------+------------+

We can use JSON_ARRAYAGG() to aggregate all those pet names into a single JSON array:

SELECT JSON_ARRAYAGG( PetName )
FROM Pets;

Result:

+--------------------------------+
| JSON_ARRAYAGG( PetName )       |
+--------------------------------+
| ["Fluffy", "Fetch", "Scratch"] |
+--------------------------------+

This can be a handy function for when we’re grouping by another column.

Example:

SELECT 
    pt.PetType,
    JSON_ARRAYAGG( p.PetName )
FROM Pets p JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
GROUP BY pt.PetType;

Result:

+---------+----------------------------+
| PetType | JSON_ARRAYAGG( p.PetName ) |
+---------+----------------------------+
| Cat     | ["Fluffy", "Scratch"]      |
| Dog     | ["Fetch"]                  |
+---------+----------------------------+

The JSON_OBJECTAGG() Function

The JSON_OBJECTAGG() function is similar to the JSON_ARRAYAGG() function in that it allows us to output JSON from a SQL query. However, JSON_OBJECTAGG() is for creating a JSON object containing key-value pairs, instead of an array.

Suppose we run the following query:

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 modify our query to use JSON_OBJECTAGG():

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}                                                                      |
+-----------------+-----------------------------------------------------------------------------------------+

Here, we grouped the results by the State column, and put the Name and Population columns into the relevant JSON object, based on the state it belongs to.

The CAST() Function

We can also use the CAST() function to cast a value of another type to a JSON type:

SELECT CAST('{"City": "Cairns", "Population": 139693}' AS JSON);

Result:

+----------------------------------------------------------+
| CAST('{"City": "Cairns", "Population": 139693}' AS JSON) |
+----------------------------------------------------------+
| {"City": "Cairns", "Population": 139693}                 |
+----------------------------------------------------------+

The CONVERT() Function

The CONVERT() function allows us to do the same thing – cast a value of another type to a JSON type. It just uses a slightly different syntax:

SELECT CONVERT('{"City": "Cairns", "Population": 139693}', JSON);

Result:

+-----------------------------------------------------------+
| CONVERT('{"City": "Cairns", "Population": 139693}', JSON) |
+-----------------------------------------------------------+
| {"City": "Cairns", "Population": 139693}                  |
+-----------------------------------------------------------+