How the JSON_MERGE_PATCH() function works in Mariadb?

The JSON_MERGE_PATCH() function is a JSON function that merges two or more JSON documents and returns the result as a JSON document. It follows the rules of the RFC 7396 standard, which defines a JSON merge patch algorithm.

The JSON_MERGE_PATCH() function is useful for updating or modifying JSON documents in a simple and efficient way. It can also handle nested JSON objects and arrays.

Syntax

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

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

The function takes two or more JSON documents as arguments and returns a JSON document as the result. The first argument is the target JSON document, and the rest are the source JSON documents. The function applies the changes from the source documents to the target document and returns the modified target document.

The function follows these rules when merging the JSON documents:

If the target document is not a JSON object or array, it is replaced by the first source document.
If the first source document is not a JSON object or array, it replaces the target document.
If both the target and the first source document are JSON objects, the function merges their members as follows:
- For each key-value pair in the source object, if the key does not exist in the target object, the pair is added to the target object.
- For each key-value pair in the source object, if the key exists in the target object and the value is null, the pair is removed from the target object.
- For each key-value pair in the source object, if the key exists in the target object and the value is not null, the function recursively applies the merge patch algorithm to the values of the target and source objects.
If both the target and the first source document are JSON arrays, the function replaces the target array with the source array.
If the target and the first source document have different types, the function replaces the target document with the source document.
If there are more than two arguments, the function applies the merge patch algorithm to the result of the previous merge and the next source document, until all arguments are processed.

Examples

Example 1: Merging two JSON objects

In this example, we have two JSON objects that represent some information about a person. We want to merge them and update the person’s name, age, and hobbies.

SELECT JSON_MERGE_PATCH(
  '{"name": "Alice", "age": 25, "hobbies": ["reading", "cooking"]}',
  '{"name": "Bob", "age": 30, "hobbies": null}'
) AS result;

+----------------------------+
| result                     |
+----------------------------+
| {"name": "Bob", "age": 30} |
+----------------------------+

The function replaces the target object’s name and age with the source object’s name and age, and removes the hobbies key-value pair from the target object, since the source object’s value is null.

Example 2: Merging a JSON object and a JSON array

In this example, we have a JSON object that represents some information about a product, and a JSON array that represents some reviews of the product. We want to merge them and add the reviews to the product information.

SELECT JSON_MERGE_PATCH(
  '{"id": 123, "name": "Laptop", "price": 999.99}',
  '["Good quality", "Fast delivery", "Easy to use"]'
) AS result;

+--------------------------------------------------+
| result                                           |
+--------------------------------------------------+
| ["Good quality", "Fast delivery", "Easy to use"] |
+--------------------------------------------------+

The function replaces the target object with the source array, since they have different types.

Example 3: Merging a JSON object and a non-JSON value

In this example, we have a JSON object that represents some information about a book, and a non-JSON value that represents the book’s rating. We want to merge them and add the rating to the book information.

SELECT JSON_MERGE_PATCH(
  '{"title": "The Hitchhiker''s Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction"}',
  4.5
) AS result;

+--------+
| result |
+--------+
| 4.5    |
+--------+

The function replaces the target object with the source value, since the source value is not a JSON object or array.

Example 4: Merging nested JSON objects

In this example, we have two JSON objects that represent some information about a student. The objects have nested JSON objects that represent the student’s address and grades. We want to merge them and update the student’s name, address, and grades.

SELECT JSON_MERGE_PATCH(
  '{"name": "John", "address": {"city": "New York", "zip": 10001}, "grades": {"math": 90, "english": 85}}',
  '{"name": "Jane", "address": {"state": "NY", "zip": null}, "grades": {"math": 95, "science": 80}}'
) AS result;

+------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------+
| {"name": "Jane", "address": {"city": "New York", "state": "NY"}, "grades": {"math": 95, "english": 85, "science": 80}} |
+------------------------------------------------------------------------------------------------------------------------+

The function merges the nested JSON objects recursively, following the same rules as before. It updates the target object’s name, adds the state key-value pair to the address object, removes the zip key-value pair from the address object, updates the math grade, and adds the science grade to the grades object.

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

JSON_MERGE_PRESERVE() function: This function is similar to the JSON_MERGE_PATCH() function, but it preserves the original JSON documents instead of applying the merge patch algorithm. It also concatenates JSON arrays instead of replacing them.
JSON_SET() function: This function inserts or updates data in a JSON document and returns the result. It takes a JSON document and one or more pairs of path and value arguments. The function inserts or updates the values in the JSON document at the specified paths. If the path does not exist, the function adds it. If the path exists, the function replaces the value.

For example, the following query uses the JSON_SET() function to add a rating key-value pair to a JSON object that represents a book:

SELECT JSON_SET(
  '{"title": "The Hitchhiker''s Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction"}',
  '$.rating', 4.5
) AS result;

+-------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------+
| {"title": "The Hitchhiker's Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction", "rating": 4.5} |
+-------------------------------------------------------------------------------------------------------------------------+

Conclusion

The JSON_MERGE_PATCH() function is a useful JSON function that can merge two or more JSON documents and return the result as a JSON document. It follows the rules of the RFC 7396 standard, which defines a JSON merge patch algorithm. The function can handle nested JSON objects and arrays, and can update or modify JSON documents in a simple and efficient way. There are also some other JSON functions in Mariadb that are related to the JSON_MERGE_PATCH() function, such as JSON_MERGE_PRESERVE(), JSON_PATCH(), and JSON_SET(). These functions can provide different ways of manipulating JSON data in Mariadb.