MariaDB JSON_REMOVE() Function

In MariaDB, JSON_REMOVE() is a built-in function that removes the data specified by path from a JSON document.

MariaDB `JSON_REMOVE()` Syntax

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

JSON_REMOVE(json, path[, path] ...)

Parameters

json: Required. A JSON document.
path: Required. A valid path expression that cannot contain * or **.

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

Return value

The MariaDB JSON_REMOVE() function removes the data specified by path from a JSON document and returns the modified JSON document.

You can provide multiple path expressions for deletion via parameters. Multiple path parameters will be executed sequentially from left to right. By the time the next parameter is executed, the JSON document may have changed.

If the specified path does not exist in the JSON, this function returns the original document.

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

MariaDB `JSON_REMOVE()` Examples

Arrays

The following statement JSON_REMOVE() uses to delete elements with positions 0 and 3 from a JSON array.

SELECT JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]');

Output:

+--------------------------------------------------+
| JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]') |
+--------------------------------------------------+
| [1, 2]                                           |
+--------------------------------------------------+

Here, you may be confused, why we use $[2] to delete the data at index position 3? This is because when there are multiple paths, they JSON_REMOVE() are executed sequentially from left to right, and the steps are as follows:

First, execute JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]'), and it returns [1, 2, [3, 4]].
Second, executed JSON_REMOVE('[1, 2, [3, 4]]', '$[2]'), and it returns [1, 2].

Objects

The following statement uses JSON_REMOVE() to remove a member from a JSON object.

SELECT JSON_REMOVE('{"x": 1, "y": 2}', '$.x');

Output:

+----------------------------------------+
| JSON_REMOVE('{"x": 1, "y": 2}', '$.x') |
+----------------------------------------+
| {"y": 2}                               |
+----------------------------------------+

Conclusion