MariaDB JSON_ARRAY() Function

In MariaDB, JSON_ARRAY() is a built-in function that returns a JSON array containing all parameters.

MariaDB JSON_ARRAY() Syntax

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

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

Parameters

value1[, value2[, ...]]

Optional. Some values, they will be put in JSON array.

Return value

The JSON_ARRAY() function evaluates all values ​​in the parameters and returns a JSON array containing all parameters.

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 some common examples to show the usages 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() |
+--------------+
| []           |
+--------------+

Conclusion

In MariaDB, JSON_ARRAY() is a built-in function that returns a JSON array containing all parameters.