How the JSON_ARRAY() function works in Mariadb?

The JSON_ARRAY() function is a JSON function that creates a JSON array from a list of values.

Posted on

The JSON_ARRAY() function is a JSON function that creates a JSON array from a list of values. The function takes zero or more values as arguments and returns a JSON array that contains the values in the same order. The values can be any valid JSON data type, such as numbers, strings, booleans, nulls, objects, or arrays.

Syntax

The syntax of the JSON_ARRAY() function is as follows:

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

Where val is a JSON value.

Examples

Example 1: Creating an empty array

In this example, we use the JSON_ARRAY() function to create an empty JSON array.

SELECT JSON_ARRAY() AS result;

The output is:

+--------+
| result |
+--------+
| []     |
+--------+

This means that the function returns an empty array.

Example 2: Creating an array with different types of values

In this example, we use the JSON_ARRAY() function to create a JSON array that contains different types of values, such as numbers, strings, booleans, and nulls.

SELECT JSON_ARRAY(1, 'a', true, null) AS result;

The output is:

+----------------------+
| result               |
+----------------------+
| [1, "a", true, null] |
+----------------------+

This means that the function returns an array with the values 1, "a", true, and null.

Example 3: Creating an array with nested arrays

In this example, we use the JSON_ARRAY() function to create a JSON array that contains nested arrays.

SELECT JSON_ARRAY(JSON_ARRAY(1, 2), JSON_ARRAY(3, 4)) AS result;

The output is:

+------------------+
| result           |
+------------------+
| [[1, 2], [3, 4]] |
+------------------+

This means that the function returns an array with two subarrays: [1, 2] and [3, 4].

Example 4: Creating an array with objects

In this example, we use the JSON_ARRAY() function to create a JSON array that contains objects.

SELECT JSON_ARRAY(JSON_OBJECT('name', 'Bob'), JSON_OBJECT('name', 'Alice')) AS result;

The output is:

+--------------------------------------+
| result                               |
+--------------------------------------+
| [{"name": "Bob"}, {"name": "Alice"}] |
+--------------------------------------+

This means that the function returns an array with two objects: {"name": "Bob"} and {"name": "Alice"}.

Example 5: Creating an array with a variable number of arguments

In this example, we use the JSON_ARRAY() function to create a JSON array that contains a variable number of arguments. We use the CONCAT() function to concatenate the arguments into a comma-separated string, and then use the PREPARE and EXECUTE statements to execute the dynamic SQL statement.

SET @args = CONCAT('1', ', ', '2', ', ', '3');
SET @sql = CONCAT('SELECT JSON_ARRAY(', @args, ') AS result');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The output is:

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

This means that the function returns an array with the values 1, 2, and 3.

There are some other JSON functions that are related to the JSON_ARRAY() function. Here are some of them:

  • JSON_OBJECT(): This function creates a JSON object from a list of key-value pairs. For example, JSON_OBJECT('name', 'Bob', 'age', 25) returns {"name": "Bob", "age": 25}.
  • JSON_ARRAY_APPEND(): This function appends values to the end of an array within a JSON document. The function takes a JSON document and one or more pairs of arguments that consist of a path and a value. The function returns a modified JSON document with the values appended to the array at the specified path. If the path does not exist, a new array is created. If the path points to a non-array value, an error occurs.
  • JSON_ARRAY_INSERT(): This function inserts values into an array within a JSON document. The function takes a JSON document and one or more pairs of arguments that consist of a path and a value. The function returns a modified JSON document with the values inserted into the array at the specified path. If the path does not exist, an error occurs. If the path points to a non-array value, an error occurs.

Conclusion

The JSON_ARRAY() function is a useful JSON function that can create a JSON array from a list of values. The function takes zero or more values as arguments and returns a JSON array that contains the values in the same order. The values can be any valid JSON data type, such as numbers, strings, booleans, nulls, objects, or arrays. There are also some other related functions that can create, append, insert, or aggregate JSON arrays, such as JSON_OBJECT(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), and JSON_ARRAYAGG().