The JSON_REMOVE function in MySQL is used to remove a specified element from a JSON document. It is part of the JSON functions introduced in MySQL version 5.7.8 and later. The function takes two arguments: the JSON document and the path to the element that needs to be removed. The result is a new JSON document with the specified element removed.

Syntax

Here is the syntax for the JSON_REMOVE function:

JSON_REMOVE(json_document, path)

json_document: The JSON document from which you want to remove an element.
path: The path to the element that you want to remove.

Example

Now, let’s look at an example to illustrate the usage of the JSON_REMOVE function:

Suppose you have the following JSON document stored in a MySQL table:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "city": "New York",
    "zipcode": "10001"
  },
  "phoneNumbers": ["123-456-7890", "987-654-3210"]
}

If you want to remove the “zipcode” from the “address” object, you can use the JSON_REMOVE function like this:

SELECT JSON_REMOVE(
    '{"name": "John Doe", "age": 30, "address": {"city": "New York", "zipcode": "10001"}, "phoneNumbers": ["123-456-7890", "987-654-3210"]}',
    '$.address.zipcode'
) AS modified_json;

The result will be:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "city": "New York"
  },
  "phoneNumbers": ["123-456-7890", "987-654-3210"]
}

In this example, the JSON_REMOVE function removed the “zipcode” element from the “address” object within the JSON document.

It’s important to note that the JSON_REMOVE function does not modify the original JSON document; instead, it returns a new JSON document with the specified element removed. If you want to update the original document in a table, you would use an UPDATE statement in combination with the JSON_SET or JSON_REPLACE function.