How the JSON_REMOVE() function works in Mariadb?

The JSON_REMOVE() function is a built-in function in Mariadb that allows you to remove one or more elements from a JSON document at a given path. It can be useful when you want to delete or filter out some unwanted or unnecessary JSON data.

Syntax

The syntax of the JSON_REMOVE() function is as follows:

JSON_REMOVE(json_doc, path1, ..., pathN)

The function takes one or more arguments, as follows:

• json_doc: This is the JSON document to remove from. It must be a valid JSON document or a column that contains JSON documents.
• path1, ..., pathN: These are the paths to the elements to remove. They must be valid JSON path expressions or columns that contain JSON path expressions.

The function returns a new JSON document that is the same as the original JSON document, except for the removed elements.

Examples

Example 1: Removing an element from a JSON array

In this example, we use the JSON_REMOVE() function to remove an element from a JSON array.

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

The output is:

+--------------+
| modified_doc |
+--------------+
| [1, 3]       |
+--------------+

As you can see, the function returns a JSON array that is the same as the original JSON array, except for the second element (2) that is removed.

Example 2: Removing an element from a JSON object

In this example, we use the JSON_REMOVE() function to remove an element from a JSON object.

SELECT JSON_REMOVE(
  '{"name": "Alice", "age": 25, "gender": "female"}',
  '$.age'
) AS modified_doc;

The output is:

+---------------------------------------+
| modified_doc                          |
+---------------------------------------+
| {"name": "Alice", "gender": "female"} |
+---------------------------------------+

As you can see, the function returns a JSON object that is the same as the original JSON object, except for the age key-value pair that is removed.

Example 3: Removing multiple elements from a JSON document

In this example, we use the JSON_REMOVE() function to remove multiple elements from a JSON document.

SELECT JSON_REMOVE(
  '{"name": "Alice", "age": 25, "gender": "female", "hobbies": ["reading", "writing", "coding"], "friends": [{"name": "Bob", "age": 30, "gender": "male"}, {"name": "Carol", "age": 28, "gender": "female"}]}',
  '$.hobbies',
  '$.friends[1]'
) AS modified_doc;

The output is:

+-------------------------------------------------------------------------------------------------------------+
| modified_doc                                                                                                |
+-------------------------------------------------------------------------------------------------------------+
| {"name": "Alice", "age": 25, "gender": "female", "friends": [{"name": "Bob", "age": 30, "gender": "male"}]} |
+-------------------------------------------------------------------------------------------------------------+

As you can see, the function returns a JSON document that is the same as the original JSON document, except for the hobbies array and the second element of the friends array that are removed.

Related Functions

There are some other functions in Mariadb that are related to the JSON_REMOVE() function. Here are some of them:

• JSON_INSERT() function: This function inserts one or more elements into a JSON document at a given path. It takes a JSON document and one or more pairs of path and value arguments, and returns a new JSON document that has the same values as the original JSON document, except for the inserted values. For example, JSON_INSERT('{"a": "b", "c": "d"}', '$.e', 'f') returns {"a": "b", "c": "d", "e": "f"}.
• JSON_REPLACE() function: This function replaces one or more elements in a JSON document at a given path. It takes a JSON document and one or more pairs of path and value arguments, and returns a new JSON document that has the same values as the original JSON document, except for the replaced values. For example, JSON_REPLACE('{"a": "b", "c": "d"}', '$.c', 'e') returns {"a": "b", "c": "e"}.
• JSON_SET() function: This function sets one or more elements in a JSON document at a given path. It takes a JSON document and one or more pairs of path and value arguments, and returns a new JSON document that has the same values as the original JSON document, except for the set values. It inserts the values if the paths do not exist, or replaces the values if the paths do exist. For example, JSON_SET('{"a": "b", "c": "d"}', '$.c', 'e', '$.f', 'g') returns {"a": "b", "c": "e", "f": "g"}.

Conclusion

The JSON_REMOVE() function is a useful function in Mariadb that allows you to remove one or more elements from a JSON document at a given path. It can handle different types of JSON documents, such as arrays, objects, or scalars. It returns a new JSON document that is the same as the original JSON document, except for the removed elements. There are also some other functions that are related to the JSON_REMOVE() function, such as JSON_INSERT(), JSON_REPLACE(), and JSON_SET(). You can use these functions to manipulate JSON data in different ways.