In MySQL, the JSON_REMOVE() function removes data from a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to remove data from. You can provide multiple paths if required.

Syntax

The syntax goes like this:

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

Where json_doc is the JSON document, and path is the path to remove data from.

The path arguments are evaluated left to right. The document produced by evaluating one path becomes the new value against which the next path is evaluated.

The first argument must be a valid JSON document, otherwise an error occurs.

Also, the path argument must be a valid path expression, and it cannot be $ or contains a * or ** wildcard, otherwise an error will occur.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_REMOVE('{"a": 1, "b": 2, "c": 3}', '$.b') AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| {"a": 1, "c": 3} |
+------------------+

In this case, we removed the key/value pair with the key of b. This is because we specified $.b as the second argument.

Here’s another example:

SELECT 
  JSON_REMOVE('{"Name": "Homer", "Gender": "Male", "Age": 39}', '$.Age') AS 'Result';

Result:

+-------------------------------------+
| Result                              |
+-------------------------------------+
| {"Name": "Homer", "Gender": "Male"} |
+-------------------------------------+

Example 2 – Non-Existent Path

If you specify a path that doesn’t exist, nothing is removed. The original JSON document is returned without modification.

SELECT 
  JSON_REMOVE('{"Name": "Homer", "Age": 39}', '$.Gender') AS 'Result';

Result:

+------------------------------+
| Result                       |
+------------------------------+
| {"Age": 39, "Name": "Homer"} |
+------------------------------+

Example 3 – Arrays

Here’s an example using an array.

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

Result:

+--------+
| Result |
+--------+
| [2, 3] |
+--------+

Arrays use zero-based numbering, so in this case, the first element is removed from the array.

Here’s another array example. This time we remove a value from a nested array.

SELECT JSON_REMOVE('[1, 2, [3, 4, 5]]', '$[2][1]') AS 'Result';

Result:

+----------------+
| Result         |
+----------------+
| [1, 2, [3, 5]] |
+----------------+

Example 4 – Multiple Paths

You can specify more than one path to remove data from multiple places in the JSON document.

Basic example:

SELECT JSON_REMOVE('[1, 2, 3, 4, 5]', '$[1]', '$[3]') AS 'Result';

Result:

+-----------+
| Result    |
+-----------+
| [1, 3, 4] |
+-----------+

As mentioned, the path arguments are evaluated left to right, and the document produced by evaluating one path becomes the new value against which the next path is evaluated.

Therefore, in this example, the 2nd path argument removes a different value to what it would’ve removed had it been the only path argument. If it had been the only path argument, it would’ve removed 4.

Here’s what I mean:

SELECT 
  JSON_REMOVE('[1, 2, 3, 4, 5]', '$[3]') AS 'One Path',
  JSON_REMOVE('[1, 2, 3, 4, 5]', '$[1]', '$[3]') AS 'Two Paths';

Result:

+--------------+-----------+
| One Path     | Two Paths |
+--------------+-----------+
| [1, 2, 3, 5] | [1, 3, 4] |
+--------------+-----------+

So you can see that when we use $[3] as the only path, it removes 4 from the array. But when we use it as the 2nd path, it removes 5 (and 4 is left untouched).

Another example, this time deleting values from an array, and a nested array:

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

Result:

+-------------+
| Result      |
+-------------+
| [2, [3, 5]] |
+-------------+

So again, even though the array is at position 2 in the original document, the first path value ($[0]) chops the outer array down, and the inner array changes to position 1.

If this makes your head spin, you can always swap the path arguments around, so that the rightmost values are removed first. That way, it won’t affect the position of the leftmost values, and therefore, you can specify the paths based on the original JSON document.

So we can change the previous code to the following, and get the same result:

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

Result:

+-------------+
| Result      |
+-------------+
| [2, [3, 5]] |
+-------------+

Example 5 – A Larger JSON Document

Here’s an example with a (slightly) larger JSON document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping", "Base Jumping"]  
    }
 }';
SELECT JSON_REMOVE(@data, '$.Person.Age', '$.Person.Hobbies[2]') AS 'Result';

Result:

+------------------------------------------------------------------+
| Result                                                           |
+------------------------------------------------------------------+
| {"Person": {"Name": "Homer", "Hobbies": ["Eating", "Sleeping"]}} |
+------------------------------------------------------------------+