MariaDB JSON_MERGE_PRESERVE() Function

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

JSON_MERGE_PRESERVE() is a synonym for JSON_MERGE() that is deprecated. You should use the JSON_MERGE_PATCH() function.

MariaDB JSON_MERGE_PRESERVE() Syntax

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

JSON_MERGE_PRESERVE(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_PRESERVE'.

Return value

The MariaDB JSON_MERGE_PRESERVE() function returns a JSON document that combines multiple JSON documents specified by parameters. JSON_MERGE_PRESERVE() 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_PRESERVE() function will return NULL.

MariaDB JSON_MERGE_PRESERVE() Examples

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

Merge arrays

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

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

Output:

+------------------------------------------------------+
| JSON_MERGE_PRESERVE('[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_PRESERVE() function to merge two or more JSON objects.

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

Output:

+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"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_PRESERVE() function to merge two or more scalar values.

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

Output:

+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('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_PRESERVE() function to merge an array and a object.

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

Output:

+-------------------------------------------+
| JSON_MERGE_PRESERVE('{"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_PRESERVE() is a built-in function that merges two or more JSON documents and returns the result.