MariaDB JSON_ARRAY(): Syntax and Examples

MariaDB JSON_ARRAY() returns a JSON array containing the values you provide. The argument list may be empty. See the official MariaDB JSON_ARRAY reference.

MariaDB `JSON_ARRAY()` Syntax

Here is the syntax for the MariaDB JSON_ARRAY() function:

JSON_ARRAY([value[, value2] ...])

Parameters

value[, value2, ...]: Zero or more SQL values to place in the array. If you provide values, separate them with commas. An empty list returns [].

Return value

JSON_ARRAY() converts each SQL value to its JSON representation. SQL NULL becomes JSON null; TRUE and FALSE become JSON Booleans; and date/time values become JSON strings.

Here are some conversions that might happen:

TRUE is converted to true
FALSE is converted to false
NULL is converted to null
date, time, datetime is converted to a string

MariaDB `JSON_ARRAY()` Examples

Here are examples of the MariaDB JSON_ARRAY() function.

Example 1

SELECT JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW());

Output:

+---------------------------------------------------------------+
| JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW())              |
+---------------------------------------------------------------+
| [123, "abc", null, true, false, "2022-04-18 07:47:23.000000"] |
+---------------------------------------------------------------+

Nested arrays

SELECT JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec'));

Output:

+------------------------------------------------------------+
| JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec')) |
+------------------------------------------------------------+
| [[123, 456], ["abc", "dec"]]                               |
+------------------------------------------------------------+

Here, we use the result of JSON_ARRAY() as the parameter of JSON_ARRAY().

Json objects

SELECT JSON_ARRAY(
        JSON_OBJECT('name', 'Jim', 'age', 20),
        JSON_OBJECT('name', 'Tim', 'age', 18)
    ) AS objct_array;

Output:

+----------------------------------------------------------+
| objct_array                                              |
+----------------------------------------------------------+
| [{"age": 20, "name": "Jim"}, {"age": 18, "name": "Tim"}] |
+----------------------------------------------------------+

Here, we use the result of JSON_OBJECT() as the parameter of JSON_ARRAY().

Escape characters

The JSON_ARRAY() function automatically escapes double quotes with a backslash.

SELECT JSON_ARRAY('The "right" time');

Output:

+--------------------------------+
| JSON_ARRAY('The "right" time') |
+--------------------------------+
| ["The \"right\" time"]         |
+--------------------------------+

Empty string

The JSON_ARRAY() function treats passed empty strings as valid strings and adds them to the array:

SELECT JSON_ARRAY('', '', '');

Output:

+------------------------+
| JSON_ARRAY('', '', '') |
+------------------------+
| ["", "", ""]           |
+------------------------+

Empty array

If you don’t pass any arguments, the JSON_ARRAY() function will return an empty array:

SELECT JSON_ARRAY();

Output:

+--------------+
| JSON_ARRAY() |
+--------------+
| []           |
+--------------+