MySQL JSON_MERGE() Function

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

The JSON_MERGE() function is exactly the same as JSON_MERGE_PRESERVE().

Note: MySQL the JSON_MERGE() function haves been deprecated in MySQL 8.0.3 and may be removed in a future release

`JSON_MERGE()` Syntax

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

JSON_MERGE(json1, json2, ...)

Parameters

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

Return value

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

Here are some examples of JSON_MERGE().

Merge arrays

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

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

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

+--------------------------------------------+
| JSON_MERGE('{"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() function to merge two or more JSON objects.

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

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

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

+----------------------------------+
| JSON_MERGE('{"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() Syntax