How the JSON_REPLACE() function works in Mariadb?

The JSON_REPLACE() function is a built-in function in Mariadb that allows you to replace one or more elements in a JSON document at a given path. It can be useful when you want to update or modify some existing JSON data.

Syntax

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

JSON_REPLACE(json_doc, path1, value1, ..., pathN, valueN)

The function takes one or more arguments, as follows:

• json_doc: This is the JSON document to replace in. It must be a valid JSON document or a column that contains JSON documents.
• path1, ..., pathN: These are the paths to the elements to replace. They must be valid JSON path expressions or columns that contain JSON path expressions.
• value1, ..., valueN: These are the values to replace with. They can be any valid JSON values or columns that contain JSON values.

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

Examples

Example 1: Replacing an element in a JSON array

In this example, we use the JSON_REPLACE() function to replace an element in a JSON array.

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

The output is:

+--------------+
| modified_doc |
+--------------+
| [1, 4, 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 replaced with 4.

Example 2: Replacing an element in a JSON object

In this example, we use the JSON_REPLACE() function to replace an element in a JSON object.

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

The output is:

+--------------------------------------------------+
| modified_doc                                     |
+--------------------------------------------------+
| {"name": "Alice", "age": 26, "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 replaced with 26.

Example 3: Replacing multiple elements in a JSON document

In this example, we use the JSON_REPLACE() function to replace multiple elements in a JSON document.

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

The output is:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| modified_doc                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"name": "Alice", "age": 25, "gender": "female", "hobbies": ["reading", "writing", "gaming"], "friends": [{"name": "Bob", "age": 30, "gender": "male"}, {"name": "Cathy", "age": 28, "gender": "female"}]} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As you can see, the function returns a JSON document that is the same as the original JSON document, except for the third element of the hobbies array and the name of the second element of the friends array that are replaced with gaming and Cathy, respectively.

Related Functions

There are some other functions in Mariadb that are related to the JSON_REPLACE() 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_REMOVE() function: This function removes one or more elements from a JSON document at a given path. It takes a JSON document and one or more path arguments, and returns a new JSON document that is the same as the original JSON document, except for the removed elements. For example, JSON_REMOVE('{"a": "b", "c": "d"}', '$.c') returns {"a": "b"}.
• 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_REPLACE() function is a useful function in Mariadb that allows you to replace one or more elements in 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 replaced elements. There are also some other functions that are related to the JSON_REPLACE() function, such as JSON_INSERT(), JSON_REMOVE(), and JSON_SET(). You can use these functions to manipulate JSON data in different ways.