MySQL includes a number of functions for working with JSON documents. Among these are the JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE() functions.

Both of these functions merge two or more JSON documents and return the result. However, there are certain cases where these functions will return a different result. You need to be aware of this before including them in any of your queries.

Syntax

First, here’s the syntax for each function:

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

Where json_doc are the JSON documents to be merged.

So both functions accept two or more arguments, each representing the JSON documents to be merged.

The Difference

Both functions work exactly the same, with the following two exceptions:

• JSON_MERGE_PATCH() removes any member in the first object with a matching key in the second object, provided that the value associated with the key in the second object is not JSON null.
• If the second object has a member with a key matching a member in the first object, JSON_MERGE_PATCH() replaces the value in the first object with the value in the second object, whereas JSON_MERGE_PRESERVE() appends the second value to the first value.

So basically, they differ in the way they handle duplicate keys.

Example

Here’s an example to demonstrate the difference between these two functions.

SELECT 
  JSON_MERGE_PATCH('{"Name": "Bartholomew"}', '{"Name": "Bart"}') JSON_MERGE_PATCH,
  JSON_MERGE_PRESERVE('{"Name": "Bartholomew"}', '{"Name": "Bart"}') JSON_MERGE_PRESERVE;

Result:

+------------------+-----------------------------------+
| JSON_MERGE_PATCH | JSON_MERGE_PRESERVE               |
+------------------+-----------------------------------+
| {"Name": "Bart"} | {"Name": ["Bartholomew", "Bart"]} |
+------------------+-----------------------------------+

We can see that JSON_MERGE_PATCH() replaced the first object’s value (Bartholomew) with the second objects value (Bart).

JSON_MERGE_PRESERVE() on the other hand, created an array and populated it with both values.

Example 2 – Arrays

Here’s an example of merging two arrays of the same name:

SELECT 
  JSON_MERGE_PATCH('{"Hobbies": ["Trouble", "Mischief"]}', '{"Hobbies": ["Skateboarding"]}') JSON_MERGE_PATCH,
  JSON_MERGE_PRESERVE('{"Hobbies": ["Trouble", "Mischief"]}', '{"Hobbies": ["Skateboarding"]}') JSON_MERGE_PRESERVE;

Result:

+--------------------------------+-------------------------------------------------------+
| JSON_MERGE_PATCH               | JSON_MERGE_PRESERVE                                   |
+--------------------------------+-------------------------------------------------------+
| {"Hobbies": ["Skateboarding"]} | {"Hobbies": ["Trouble", "Mischief", "Skateboarding"]} |
+--------------------------------+-------------------------------------------------------+

So in this case, JSON_MERGE_PATCH() has replaced all elements in the first array with the element in the second array.

JSON_MERGE_PRESERVE() has simply combined the values of both arrays into one.

Obviously, if the arrays have different names, they’ll end up as separate arrays (but within the same JSON document). So in such cases, both functions will return the same result.

SELECT 
  JSON_MERGE_PATCH('{"Hobbies": ["Trouble", "Mischief"]}', '{"Hobby": ["Skateboarding"]}') AS Result
UNION ALL
SELECT 
  JSON_MERGE_PRESERVE('{"Hobbies": ["Trouble", "Mischief"]}', '{"Hobby": ["Skateboarding"]}');

Result:

+------------------------------------------------------------------+
| Result                                                           |
+------------------------------------------------------------------+
| {"Hobby": ["Skateboarding"], "Hobbies": ["Trouble", "Mischief"]} |
| {"Hobby": ["Skateboarding"], "Hobbies": ["Trouble", "Mischief"]} |
+------------------------------------------------------------------+