MySQL JSON_MERGE_PATCH() Function

In MySQL, a JSON_MERGE_PATCH() function performs a replace merge on two or more JSON documents and returns the merged result.

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

`JSON_MERGE_PATCH()` Syntax

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

JSON_MERGE_PATCH(json1, json2, ...)

Parameters

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

Return value

In MySQL, the JSON_MERGE_PATCH() function returns a JSON document that combines multiple JSON documents specified by the parameter. JSON_MERGE_PATCH() performs a replace merge, that is, when the key value is the same, only the last value is retained. The rules for merging are as follows:

If the first argument is not an object, the result of merging is the same as merging an empty object with the second argument.
If the second argument is not an object, the result of the merge is the second argument.
If both arguments are objects, the merged object has the following members:
- All members that only exist in the first object
- All members that only exist in the second object and whose value is not null
- All members existing in the second object and whose value is not null, and has a corresponding member of the same key in the first object

That is, the result of merging two objects is an object. If the two parameters are of different JSON types or neither is a JSON object, the merged result is the second parameter.

The JSON_MERGE_PATCH() function will return NULL if any argument 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_PATCH()` Examples

Here are some examples of JSON_MERGE_PATCH().

merge nonobjects

The following example demonstrates how to use the JSON_MERGE_PATCH() function to merge two JSON documents of nonobject types.

SELECT
    JSON_MERGE_PATCH('2', 'true') as `2 + true`,
    JSON_MERGE_PATCH('true', '2') as `true + 2`,
    JSON_MERGE_PATCH('[1, 2]', '2') as `[1, 2] + 2`,
    JSON_MERGE_PATCH('2', '[1, 2]') as `2 + [1, 2]`,
    JSON_MERGE_PATCH('[1, 2]', '[2, 3]') as `[1, 2] + [2, 3]`;

+----------+----------+------------+------------+-----------------+
| 2 + true | true + 2 | [1, 2] + 2 | 2 + [1, 2] | [1, 2] + [2, 3] |
+----------+----------+------------+------------+-----------------+
| true     | 2        | 2          | [1, 2]     | [2, 3]          |
+----------+----------+------------+------------+-----------------+

Here we see that nany parameter is a JSON object and the function returns the second parameter.

Merge objects

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

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}');

+----------------------------------------------------------+
| JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}') |
+----------------------------------------------------------+
| {"x": 2, "y": 3, "z": 7}                                 |
+----------------------------------------------------------+

If there is a key has a null value in the second parameter, the key will not appear in the result object.

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}');

+-------------------------------------------------------------+
| JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}') |
+-------------------------------------------------------------+
| {"x": 2}                                                    |
+-------------------------------------------------------------+

JSON_MERGE_PATCH() Syntax