MySQL JSON_MERGE_PRESERVE() Function

In MySQL, the JSON_MERGE_PRESERVE() function merges two or more JSON documents and return the combined result.

This function has the same syntax as JSON_MERGE_PATCH(), but their merge logic is different.

`JSON_MERGE_PRESERVE()` Syntax

Here is the syntax of the MySQL JSON_MERGE_PRESERVE() function:

JSON_MERGE_PRESERVE(json1, json2, ...)

Parameters

json1: Required. A JSON object document.
json2: Required. Another JSON object document.

Return value

In MySQL, the JSON_MERGE_PRESERVE() function returns a JSON document that is the result of merging multiple JSON documents specified by the parameter. JSON_MERGE_PRESERVE() merges 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.

The JSON_MERGE_PRESERVE() function will return NULL if any parameter is NULL.

If any parameter is not a valid JSON document, MySQL will give an error. You can use JSON_VALID() to verify the JSON document.

`JSON_MERGE_PRESERVE()` Examples

Here are some examples of JSON_MERGE_PRESERVE().

Merge arrays

The following example 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]');

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

We can find that all elements in all arrays are preserved, regardless of whether the elements are duplicates or not. And the order of the elements remains the same as the order in the parameters.

Merge objects

The following example 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}');

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

Here, since "x": 1 and "x": 2 have the same key "x", their values are merged into an array: [1, 2].

Merge scalars

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

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

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

Here, the scalars are wrapped into arrays during the merging process, so all of them are merged into one array.

Merge object and array

The following example demonstrates how to use the JSON_MERGE_PRESERVE() function to merge an object and an array.

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

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

Here, the object was autowrapped into an array first [{"x": 1}], then [{"x": 1}] and [1, 2] were merged into a new array [{"x": 1}, 1, 2].

JSON_MERGE_PRESERVE() Syntax