How the JSON_MERGE_PRESERVE() function works in Mariadb?

The JSON_MERGE_PRESERVE() function is a JSON function that merges two or more JSON documents and returns the result as a JSON document.

Posted on

The JSON_MERGE_PRESERVE() function is a JSON function that merges two or more JSON documents and returns the result as a JSON document. It 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.

The JSON_MERGE_PRESERVE() function is useful for combining JSON documents without losing any data. It can also handle nested JSON objects and arrays.

Syntax

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

JSON_MERGE_PRESERVE(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 merges the source documents into the target document and returns the merged 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, the function recursively applies the merge preserve algorithm to the values of the target and source objects.
  • If both the target and the first source document are JSON arrays, the function concatenates the target array and the source array and returns the result as a JSON 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 preserve 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 preserve the person’s name, age, and hobbies.

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

+--------------------------------------------------------------------------------------+
| result                                                                               |
+--------------------------------------------------------------------------------------+
| {"name": ["Alice", "Bob"], "age": [25, 30], "hobbies": ["reading", "cooking", null]} |
+--------------------------------------------------------------------------------------+

The function preserves the target object’s name, age, and hobbies, and adds the source object’s name, age, and hobbies as arrays.

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 preserve the product information and the reviews.

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

+--------------------------------------------------------------------------------------------------+
| result                                                                                           |
+--------------------------------------------------------------------------------------------------+
| [{"id": 123, "name": "Laptop", "price": 999.99}, "Good quality", "Fast delivery", "Easy to use"] |
+--------------------------------------------------------------------------------------------------+

The function concatenates the target object and the source array and returns the result as a JSON array.

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 preserve the book information and the rating.

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

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

The function concatenates the target object and the source value and returns the result as a JSON 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 preserve the student’s name, address, and grades.

SELECT JSON_MERGE_PRESERVE(
  '{"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": ["John", "Jane"], "address": {"city": "New York", "zip": [10001, null], "state": "NY"}, "grades": {"math": [90, 95], "english": 85, "science": 80}} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

The function merges the nested JSON objects recursively, following the same rules as before. It preserves the target object’s name, address, and grades, and adds the source object’s name, address, and grades as arrays.

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

  • JSON_MERGE_PATCH() function: This function is similar to the JSON_MERGE_PRESERVE() function, but it applies the merge patch algorithm to the JSON documents instead of preserving them. It also replaces JSON arrays instead of concatenating them.
  • JSON_ARRAY_APPEND() function: This function appends values to the end of JSON arrays and returns the result as a JSON document. It takes a JSON document and one or more pairs of path and value arguments. The function appends the values to the JSON arrays at the specified paths. If the path does not exist, the function adds it. If the path exists, but the value is not a JSON array, the function converts the value to a JSON array and appends the new value.

Conclusion

The JSON_MERGE_PRESERVE() function is a useful JSON function that can merge two or more JSON documents and return the result as a JSON document. It preserves the original JSON documents instead of applying the merge patch algorithm. It also concatenates JSON arrays instead of replacing them. The function can handle nested JSON objects and arrays, and can combine JSON documents without losing any data. There are also some other JSON functions in Mariadb that are related to the JSON_MERGE_PRESERVE() function, such as JSON_MERGE_PATCH(), JSON_ARRAY_APPEND(), and JSON_OBJECT_MERGE(). These functions can provide different ways of manipulating