In MySQL, you can use the JSON_ARRAY() function to create a JSON array from a list of values. You provide each value as a separate argument. Each argument becomes a separate element of the array.

The function also accepts an empty list (i.e. you provide no arguments). In this case, you’ll get an empty array.

Syntax

The syntax goes like this:

JSON_ARRAY([val[, val] ...])

The square brackets indicate an optional argument. Therefore, it’s quite valid to use this function without passing in any arguments.

Example 1 – Strings

Here’s an example of returning an array from a list of strings.

SELECT JSON_ARRAY('Hot', 'Warm', 'Cold') AS 'Result';

Result:

+-------------------------+
| Result                  |
+-------------------------+
| ["Hot", "Warm", "Cold"] |
+-------------------------+

Example 2 – Numbers

The list can also be numbers:

SELECT JSON_ARRAY(1, 2, 3) AS 'Result';

Result:

+-----------+
| Result    |
+-----------+
| [1, 2, 3] |
+-----------+

Example 3 – Mixed Types

And it can be a mix of strings and numbers:

SELECT JSON_ARRAY(1, 'Warm', 3) AS 'Result';

Result:

+----------------+
| Result         |
+----------------+
| [1, "Warm", 3] |
+----------------+

Example 4 – Empty Strings

It works with empty strings too.

SELECT JSON_ARRAY(1, '', 3) AS 'Result';

Result:

+------------+
| Result     |
+------------+
| [1, "", 3] |
+------------+

Example 5 – Empty Lists

As mentioned, it’s OK to provide no arguments. Doing this will result in an empty array.

SELECT JSON_ARRAY() AS 'Result';

Result:

+--------+
| Result |
+--------+
| []     |
+--------+

Example 5 – NULL Values

And yes, NULL values can also be included.

SELECT JSON_ARRAY(1, NULL, 3) AS 'Result';

Result:

+--------------+
| Result       |
+--------------+
| [1, null, 3] |
+--------------+