The JSON_MERGE_PATCH function in MySQL is used to merge two JSON objects, where the values from the second JSON object overwrite the corresponding values in the first one. If a key exists in both JSON objects, the value from the second object is used. If a key exists in the first object but not in the second, the key-value pair is retained. If a key exists in the second object but not in the first, it is added to the result.

When merging two JSON objects, the function considers the following rules:

Duplicate Keys: If a key exists in both documents, the value from the second document overwrites the value from the first document.

Missing Keys: If a key exists in the first document but not in the second document, the value from the first document remains unchanged.

Null Values: If a key exists in the second document but its value is JSON null, the key-value pair is removed from the merged document.

Nested Objects: The merge operation recursively applies to nested objects, ensuring consistent merging across the entire JSON structure.

Syntax

Here is the syntax for the JSON_MERGE_PATCH function:

JSON_MERGE_PATCH(json_doc, json_patch[, json_patch, ...])

json_doc: The original JSON document to be patched.
json_patch: One or more JSON documents that contain the changes to be applied.

Example

Now, let’s look at an example to illustrate how the JSON_MERGE_PATCH function works:

Suppose we have the following JSON documents:

SET @original_json = '{"name": "John", "age": 25, "city": "New York"}';
SET @patch_json = '{"age": 26, "city": "San Francisco", "country": "USA"}';

Applying the JSON_MERGE_PATCH function:

SELECT JSON_MERGE_PATCH(@original_json, @patch_json);

The result of this query will be:

{"name": "John", "age": 26, "city": "San Francisco", "country": "USA"}

In this example, the JSON_MERGE_PATCH function merged the original JSON document (@original_json) with the patch JSON document (@patch_json). The resulting JSON contains the updated values for “age” and “city” from the patch, and the new key-value pair “country”: “USA” has been added.

Keep in mind that JSON_MERGE_PATCH only considers the first level of keys in the JSON objects. If you need to perform a deep merge, considering nested structures, you might want to explore other options, such as using third-party libraries or writing custom logic in your application.