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