How the JSON_MERGE() function works in Mariadb?

The JSON_MERGE() function is a built-in function in Mariadb that allows you to merge two or more JSON documents into one. It can be useful when you want to combine data from different sources or update existing JSON documents with new values.

Syntax

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

JSON_MERGE(json_doc1, json_doc2, ..., json_docN)

The function takes two or more arguments, each of which must be a valid JSON document or a column that contains JSON documents. The function returns a new JSON document that contains the merged data from all the arguments.

The function follows these rules when merging JSON documents:

• If the arguments are JSON arrays, the function appends them into a single array.
• If the arguments are JSON objects, the function merges them into a single object. If there are duplicate keys, the function uses the last value for each key.
• If the arguments are JSON scalars (such as strings, numbers, booleans, or nulls), the function wraps them into an array.

Examples

Example 1: Merging JSON arrays

In this example, we use the JSON_MERGE() function to merge two JSON arrays that contain information about books.

SELECT JSON_MERGE(
  '[{"title": "The Catcher in the Rye", "author": "J.D. Salinger"}, {"title": "To Kill a Mockingbird", "author": "Harper Lee"}]',
  '[{"title": "The Hitchhiker''s Guide to the Galaxy", "author": "Douglas Adams"}, {"title": "1984", "author": "George Orwell"}]'
) AS merged_books;

The output is:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| merged_books                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"title": "The Catcher in the Rye", "author": "J.D. Salinger"}, {"title": "To Kill a Mockingbird", "author": "Harper Lee"}, {"title": "The Hitchhiker's Guide to the Galaxy", "author": "Douglas Adams"}, {"title": "1984", "author": "George Orwell"}] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As you can see, the function returns a JSON array that contains the elements from both arguments.

Example 2: Merging JSON objects

In this example, we use the JSON_MERGE() function to merge two JSON objects that contain information about a person.

SELECT JSON_MERGE(
  '{"name": "Alice", "age": 25, "gender": "female"}',
  '{"name": "Bob", "age": 30, "gender": "male", "occupation": "engineer"}'
) AS merged_person;

The output is:

+-----------------------------------------------------------------------------------------------------+
| merged_person                                                                                       |
+-----------------------------------------------------------------------------------------------------+
| {"name": ["Alice", "Bob"], "age": [25, 30], "gender": ["female", "male"], "occupation": "engineer"} |
+-----------------------------------------------------------------------------------------------------+

As you can see, the function returns a JSON object that contains the merged data from both arguments. Since there are duplicate keys (name, age, and gender), the function uses the last value for each key.

Example 3: Merging JSON scalars

In this example, we use the JSON_MERGE() function to merge three JSON scalars: a string, a number, and a boolean.

SELECT JSON_MERGE(
  '"Hello"',
  '42',
  'true'
) AS merged_scalars;

The output is:

+---------------------+
| merged_scalars      |
+---------------------+
| ["Hello", 42, true] |
+---------------------+

As you can see, the function returns a JSON array that contains the scalars wrapped into an array.

Example 4: Merging JSON documents with different types

In this example, we use the JSON_MERGE() function to merge JSON documents with different types: an array, an object, and a scalar.

SELECT JSON_MERGE(
  '[1, 2, 3]',
  '{"a": "b", "c": "d"}',
  'null'
) AS merged_docs;

The output is:

+---------------------------------------+
| merged_docs                           |
+---------------------------------------+
| [1, 2, 3, {"a": "b", "c": "d"}, null] |
+---------------------------------------+

As you can see, the function returns a JSON array that contains the JSON documents as elements.

Related Functions

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

• JSON_MERGE_PATCH() function: This function also merges two or more JSON documents into one, but it follows the RFC 7396 standard for merging. It differs from the JSON_MERGE() function in some cases, such as when merging arrays or null values. For example, JSON_MERGE_PATCH('[1, 2]', '[3, 4]') returns [3, 4], while JSON_MERGE('[1, 2]', '[3, 4]') returns [1, 2, 3, 4]. Similarly, JSON_MERGE_PATCH('{"a": "b"}', 'null') returns null, while JSON_MERGE('{"a": "b"}', 'null') returns [{"a": "b"}, null].
• JSON_MERGE_PRESERVE() function: This function is an alias for the JSON_MERGE() function. It has the same syntax and behavior as the JSON_MERGE() function.
• JSON_ARRAY_APPEND() function: This function appends values to the end of a JSON array. It takes a JSON document and one or more pairs of path and value arguments. The path arguments specify where to append the values in the JSON document. The value arguments are the values to append. For example, JSON_ARRAY_APPEND('[1, 2]', '$', 3, '$', 4) returns [1, 2, 3, 4].

Conclusion

The JSON_MERGE() function is a useful function in Mariadb that allows you to merge two or more JSON documents into one. It can handle different types of JSON documents, such as arrays, objects, and scalars. It follows some rules when merging JSON documents, such as appending arrays, merging objects, and wrapping scalars. There are also some other functions that are related to the JSON_MERGE() function, such as JSON_MERGE_PATCH(), JSON_MERGE_PRESERVE(), JSON_ARRAY_APPEND(), and JSON_OBJECT_MERGE(). You can use these functions to manipulate JSON data in different ways.