MariaDB JSON_MERGE() Function

In MariaDB, JSON_MERGE() is a built-in function that merges two or more JSON documents and returns the result.

JSON_MERGE() has been deprecated and JSON_MERGE_PRESERVE() is a synonym for it. You should use the JSON_MERGE_PATCH() function .

MariaDB JSON_MERGE() Syntax

Here is the syntax for the MariaDB JSON_MERGE() function:

JSON_MERGE(json1, json2, ...)

Parameters

json1

Required. A JSON object document.

json2

Required. A JSON object document.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_MERGE'.

Return value

The MariaDB JSON_MERGE() function returns a JSON document that combines multiple JSON documents specified by parameters. JSON_MERGE() merge multiple JSON documents according to the following rules:

  • Two arrays are merged into one array, preserving all the elements in the arrays.
  • Two objects are merged into one object, preserving all keys and values.
  • A plain value will be wrapped into an array and merged as an array
  • When merge an object and an arrays, the object are wrapped into an array and merged as an array.

If either argument is NULL, the JSON_MERGE() function will return NULL.

MariaDB JSON_MERGE() Examples

Here are some common examples to show the usages of the Mariadb JSON_MERGE() function.

merge array

The following demonstrates how to use the JSON_MERGE() function to merge two or more JSON arrays.

SELECT JSON_MERGE('[1, 2]', '[2, 3]', '[3, 4, 5]');

Output:

+---------------------------------------------+
| JSON_MERGE('[1, 2]', '[2, 3]', '[3, 4, 5]') |
+---------------------------------------------+
| [1, 2, 2, 3, 3, 4, 5]                       |
+---------------------------------------------+

From the results we can see that all elements in all arrays are preserved, regardless of whether the elements are repeated.

Merge objects

The following demonstrates how to use the JSON_MERGE() function to merge two or more JSON objects.

SELECT JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}');

Output:

+--------------------------------------------+
| JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}') |
+--------------------------------------------+
| {"x": [1, 2], "y": 3}                      |
+--------------------------------------------+

Here, because the keys of "x": 1 and "x": 2 are both "x", their values ​​are merged into one array, it is: [1, 2].

Merge scalar values

The following demonstrates how to use the JSON_MERGE() function to merge two or more scalar values.

SELECT JSON_MERGE('1', 'true', '"hello"', 'null');

Output:

+--------------------------------------------+
| JSON_MERGE('1', 'true', '"hello"', 'null') |
+--------------------------------------------+
| [1, true, "hello", null]                   |
+--------------------------------------------+

Here, the scalar values ​​are wrapped into arrays during merging, so they end up being merged into an array.

Merge arrays and objects

The following demonstrates how to use the JSON_MERGE() function to merge arrays and objects.

SELECT JSON_MERGE('{"x": 1}', '[1, 2]');

Output:

+----------------------------------+
| JSON_MERGE('{"x": 1}', '[1, 2]') |
+----------------------------------+
| [{"x": 1}, 1, 2]                 |
+----------------------------------+

Here, when merging an object and an array, the object is automatically wrapped into an array, so both the object and the elements in the original array are merged into a new array.

Conclusion

In MariaDB, JSON_MERGE() is a built-in function that merges two or more JSON documents and returns the result.