MySQL JSON_REMOVE() Function

Summary: in this tutorial, you’ll learn how to use the MySQL JSON_REMOVE() function to remove elements from a JSON document.

Introduction to the MySQL JSON_REMOVE() function

The JSON_REMOVE() function is used to remove elements from a JSON document.

Here’s the syntax of the JSON_REMOVE() function:

JSON_REMOVE(json_doc, path[, path] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_doc: This is the JSON document from which you want to remove elements.
path: This is the path expression that specifies the location of the data you want to remove.

The function returns the updated document or NULL if any argument is NULL.

If the path expression does not locate any element, the JSON_REMOVE() function will not change the document.

MySQL JSON_REMOVE() function examples

Let’s take some examples of using the JSON_REMOVE() function.

1) Removing a key from a JSON object

The following example uses the JSON_REMOVE() function to remove a property from an object within a JSON document:

SELECT 
  JSON_REMOVE(
    '{"name": "John", "age": 30}', '$.age'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| result           |
+------------------+
| {"name": "John"} |
+------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the path expression $.age specifies the age property, therefore, the JSON_REMOVE() function removes the element associated with the "age" key from the JSON document.

2) Removing an element from a JSON array

The following example uses the JSON_REMOVE() function to remove the second element in an array of a JSON document:

SELECT 
  JSON_REMOVE(
    '["PHP","MySQL","JavaScript"]', 
    '$[1]'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+
| result                |
+-----------------------+
| ["PHP", "JavaScript"] |
+-----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Handling non-existent paths

If the path doesn’t exist, the JSON_REMOVE() function does nothing and returns the original JSON document:

SELECT 
  JSON_REMOVE(
    '{"name": "John", "age": 28}', 
    '$.job'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| result                      |
+-----------------------------+
| {"age": 28, "name": "John"} |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON_REMOVE() function to remove elements from a JSON document.

Was this tutorial helpful?