MariaDB JSON_INSERT() Function

In MariaDB, JSON_INSERT() is a built-in function that inserts one or more pieces of data into a JSON document and returns the new JSON document.

You can also modify a JSON document using JSON_REPLACE() and JSON_SET().

MariaDB JSON_INSERT() Syntax

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

JSON_INSERT(json, path, value[, path2, value2] ...)

Parameters

json

Required. The modified JSON document.

path

Required. A valid path expression that cannot contain * or **.

value

Required. The data to be inserted.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_INSERT'.

Return value

The MariaDB JSON_INSERT() function inserts data into a JSON document and return a new JSON document. You can provide multiple pairs of path-value parameters to insert multiple data at once.

The MariaDB JSON_INSERT() function can insert data into paths that do not exist. If the specified path already exists in the JSON document, no data will be inserted.

If value is a string, the JSON_INSERT() function will write it to the JSON document as a string. In order to ensure that the type of the value written into the JSON document is correct, please use values of JSON type.

If path is $, the JSON_INSERT() function returns the original JSON document.

This function will return NULL if the JSON document or path is NULL.

MariaDB JSON_INSERT() Examples

Basic example

Let’s create a JSON document to demonstrate the following example:

SET @array = '[1, [2, 3], {"a": [4, 5]}]';

Use the following statement to insert an element into a JSON array:

SELECT JSON_INSERT(@array, '$[0]', 0, '$[3]', 6);

Output:

+-------------------------------------------+
| JSON_INSERT(@array, '$[0]', 0, '$[3]', 6) |
+-------------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, 6]             |
+-------------------------------------------+

In this example, we want to insert elements at the beginning of the array and at the end of the array.

We found that the data was not inserted into the beginning ($[0]) of the array. This is because the array already has a value at $[0] position, so 0 won’t be inserted. And the array has no data at the $[3] position , so 6 is inserted at the end of the array.

You can also use JSON_ARRAY_APPEND() to append data to the end of the array.

Insert JSON type data

In addition to inserting simple literal values, we can also insert complex JSON elements, such as arrays and objects.

Let’s create a JSON object document:

SET @obj = '{"x": 1}';

Now let’s insert a member y whose value is true:

SELECT JSON_INSERT(@obj, '$.y', 'true');

Output:

+----------------------------------+
| JSON_INSERT(@obj, '$.y', 'true') |
+----------------------------------+
| {"x": 1, "y": "true"}            |
+----------------------------------+

We found out that true became "true", rather than what we hoped for {"x": 1, "y": true}.

This is because, if the value parameter is a string, the JSON_INSERT() function writes it to the JSON document as a string. Let’s look at a few more similar examples:

SELECT JSON_INSERT(@obj, '$.y', '[1, 2]');

Output:

+------------------------------------+
| JSON_INSERT(@obj, '$.y', '[1, 2]') |
+------------------------------------+
| {"x": 1, "y": "[1, 2]"}            |
+------------------------------------+

or

SELECT JSON_INSERT(@obj, '$.y', '{"z": 2}');

Output:

+--------------------------------------+
| JSON_INSERT(@obj, '$.y', '{"z": 2}') |
+--------------------------------------+
| {"x": 1, "y": "{\"z\": 2}"}          |
+--------------------------------------+

To solve this problem, we can use the JSON_EXTRACT() function to convert the data into JSON type, for example:

SELECT JSON_INSERT(@obj, '$.y', JSON_EXTRACT('{"z": 2}', '$'));

Output:

+---------------------------------------------------------+
| JSON_INSERT(@obj, '$.y', JSON_EXTRACT('{"z": 2}', '$')) |
+---------------------------------------------------------+
| {"x": 1, "y": {"z": 2}}                                 |
+---------------------------------------------------------+

You can also use JSON_QUERY() or JSON_VALUE().

Incorrect parameter count

Providing no arguments results in an error:

SELECT JSON_INSERT();

Output:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_INSERT'

The same is true when you provide too few or too many arguments:

SELECT JSON_INSERT('{ "a": 1}');

Output:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_INSERT'

Conclusion

In MariaDB, JSON_INSERT() is a built-in function that inserts one or more pieces of data into a JSON document and returns the new JSON document.