How the JSON_INSERT() function works in Mariadb?

The JSON_INSERT() function is a JSON function that inserts a value or values into a JSON document at a given path or paths.

Posted on

The JSON_INSERT() function is a JSON function that inserts a value or values into a JSON document at a given path or paths. The function takes a JSON document, a path-value pair, and optionally more path-value pairs as arguments. The function returns a modified JSON document that contains the inserted value or values. If the JSON document or any of the paths are invalid, the function returns NULL.

Syntax

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

JSON_INSERT(json_doc, path, val[, path, val] ...)

Where json_doc is a valid JSON document, path is a valid JSON path expression, and val is a JSON value.

Examples

Example 1: Inserting a value into a JSON document at the root level

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_INSERT() function to insert the value 4 at the root level.

SELECT JSON_INSERT(JSON_ARRAY(1, 2, 3), '$[3]', 4) AS result;

The output is:

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

This means that the function returns a modified JSON document that contains the inserted value 4 at the root level.

Example 2: Inserting a value into a JSON document at a specified path

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_INSERT() function to insert the value "Alice" at the path $.name.

SELECT JSON_INSERT(JSON_OBJECT('age', 25, 'gender', 'female'), '$.name', 'Alice') AS result;

The output is:

+--------------------------------------------------+
| result                                           |
+--------------------------------------------------+
| {"age": 25, "gender": "female", "name": "Alice"} |
+--------------------------------------------------+

This means that the function returns a modified JSON document that contains the inserted value "Alice" at the path $.name.

Example 3: Inserting a value into a JSON document at a nested path

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_INSERT() function to insert the value "coding" at the nested path $.hobbies[1].

SELECT JSON_INSERT(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_ARRAY('sports', 'music')), '$.hobbies[2]', 'coding') AS result;

The output is:

+----------------------------------------------------------------------+
| result                                                               |
+----------------------------------------------------------------------+
| {"name": "Bob", "age": 30, "hobbies": ["sports", "music", "coding"]} |
+----------------------------------------------------------------------+

This means that the function returns a modified JSON document that contains the inserted value "coding" at the nested path $.hobbies[1].

Example 4: Inserting multiple values into a JSON document at multiple paths

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_INSERT() function to insert multiple values into the JSON document at multiple paths.

SELECT JSON_INSERT(
    JSON_OBJECT('name', 'Carol', 
                'age', 35, 
                'hobbies', JSON_ARRAY('music', 'movies')), 
    '$.gender', 'female', 
    '$.hobbies[2]', 'coding') AS result;

The output is:

+--------------------------------------------------------------------------------------------+
| result                                                                                     |
+--------------------------------------------------------------------------------------------+
| {"name": "Carol", "age": 35, "hobbies": ["music", "movies", "coding"], "gender": "female"} |
+--------------------------------------------------------------------------------------------+

This means that the function returns a modified JSON document that contains the inserted values "female" and "coding" at the paths $.gender and $.hobbies[2].

Example 5: Getting NULL if the JSON document or any of the paths are invalid

In this example, we use the JSON_INSERT() function to insert a value or values into a JSON document at a given path or paths. However, we use an invalid JSON document and an invalid path as arguments. The function returns NULL in this case.

SELECT JSON_INSERT('{"name": "Dave"', '$.age', 40) AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the JSON document is not valid (it is missing a closing brace).

SELECT JSON_INSERT(JSON_OBJECT('name', 'Eve', 'age', 40), '$**', '"female"') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the path $** is not valid (it contains a wildcard token that is not allowed in this situation).

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

  • JSON_REPLACE(): This function replaces a value or values in a JSON document at a given path or paths. The function takes a JSON document, a path-value pair, and optionally more path-value pairs as arguments. The function returns a modified JSON document that contains the replaced value or values. If the JSON document or any of the paths are invalid, the function returns NULL.
  • JSON_SET(): This function sets a value or values in a JSON document at a given path or paths. The function takes a JSON document, a path-value pair, and optionally more path-value pairs as arguments. The function returns a modified JSON document that contains the set value or values. If the JSON document or any of the paths are invalid, the function returns NULL.

Conclusion

The JSON_INSERT() function is a useful JSON function that can insert a value or values into a JSON document at a given path or paths. The function takes a JSON document, a path-value pair, and optionally more path-value pairs as arguments. The function returns a modified JSON document that contains the inserted value or values. If the JSON document or any of the paths are invalid, the function returns NULL. There are also some other related functions that can append, replace, or set values in JSON documents, such as JSON_APPEND(), JSON_REPLACE(), and JSON_SET().