MariaDB JSON_SET() Function

In MariaDB, JSON_SET() is a built-in function that inserts or updates data in a JSON document and returns the new JSON document.

JSON_SET() is equivalent to a combination of JSON_INSERT() and JSON_REPLACE(). JSON_INSERT() can only insert data, and JSON_REPLACE() can only update data.

MariaDB JSON_SET() Syntax

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

JSON_SET(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 set.

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_SET'.

Return value

The MariaDB JSON_SET() function inserts or update data in a JSON document and return the new JSON document. You can provide multiple pairs of path-value parameters to set multiple data at once.

If the path specified in the parameter exists in the JSON document, update the value matching the path; if not, insert data on the corresponding path.

If value is a string, the JSON_SET() function will write it to the JSON document as a string.

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

MariaDB JSON_SET() Examples

Let’s first create a JSON document containing JSON objects:

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

Now let’s set some data:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]');

Output:

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

We found that although the operation have been successful, there are still a small problem, that is, the array [1, 2] becomes "[1, 2]".

This is because, if the value parameter is a string, the JSON_SET() function writes it to the JSON document as a string. Let’s look at another similar example:

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

Output:

+------------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}') |
+------------------------------------------------+
| {"x": "10", "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_SET(@obj, '$.x', '10', '$.y', JSON_EXTRACT('[1, 2]', '$'));

Output:

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

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

Conclusion

In MariaDB, JSON_SET() is a built-in function that inserts or updates data in a JSON document and returns the new JSON document.