MySQL JSON_REMOVE() Function

In MySQL, the JSON_REMOVE() function removes the data specified by the path from a JSON document and returns the modified JSON document.

`JSON_REMOVE()` Syntax

Here is the syntax of the MySQL JSON_REMOVE() function:

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

Parameters

json: Required. A JSON document to remove data from.
path: Required. The path to remove. A valid path expression that cannot contain * or **.

Return value

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

You can provide multiple path expressions for deletion. Multiple path parameters are performed from left to right. So when JSON_REMOVE() performs the next parameter, the JSON document may have changed.

If the JSON document does not include all specified path, this function returns the original document.

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

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression or equals to $ or contains * or **, MySQL will give an error.

`JSON_REMOVE()` Examples

Remove from array

The following statement uses JSON_REMOVE() to remove the the forth and third elements from a JSON array.

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

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

Here, you may be confused, why use $[2] to remove data at index 3? This is because when there are multiple paths, JSON_REMOVE() performs the paths from left to right. The steps are as follows:

First, execute JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]'), and returns a JSON document [1, 2, [3, 4]].
Then, execute JSON_REMOVE('[1, 2, [3, 4]]', '$[2]'), and returned a JSON document [1, 2].

Remove from object

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

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

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

JSON_REMOVE() Syntax