How the JSON_SEARCH() function works in Mariadb?

The JSON_MERGE() 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() function is a JSON function that merges two or more JSON documents and returns the result as a JSON document. It is an alias for the JSON_MERGE_PRESERVE() function, which preserves the original JSON documents instead of applying the merge patch algorithm. It also concatenates JSON arrays instead of replacing them.

The JSON_MERGE() 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() function is as follows:

JSON_MERGE(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 the same rules as the JSON_MERGE_PRESERVE() function when merging the JSON documents.

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(
  '{"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(
  '{"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(
  '{"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(
  '{"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() function. Here are some of them:

  • JSON_MERGE_PATCH() function: This function is similar to the JSON_MERGE() 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.