In MySQL, the JSON_MERGE_PATCH() function performs an RFC 7396 compliant merge of two or more JSON documents, without preserving members having duplicate keys.

You provide the JSON documents as arguments.

Syntax

The syntax goes like this:

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

Where json_doc are the JSON documents to be merged. If any of the documents are invalid, an error is raised.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_MERGE_PATCH('{"Name": "Homer"}', '{"Age": 39}') Result;

Result:

+------------------------------+
| Result                       |
+------------------------------+
| {"Age": 39, "Name": "Homer"} |
+------------------------------+

So in this example, we merged two separate objects into one object.

Example 2 – Duplicate Keys

As mentioned, this function doesn’t preserve members with duplicate keys. Example:

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

Result:

+------------------+
| Result           |
+------------------+
| {"Name": "Bart"} |
+------------------+

So in this case, Bart won.

If you need to preserve members with duplicate keys use the JSON_MERGE_PRESERVE() function instead. Using that function for this example would turn Name into an array containing both Bartholomew and Bart. Like this:

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

Result:

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

Example 3 – Multiple Members

Here’s another example, but with an extra member in the object:

SELECT 
  JSON_MERGE_PATCH('{"Name": "Bartholomew", "Age": 10}', '{"Name": "Bart"}') Result;

Result:

+-----------------------------+
| Result                      |
+-----------------------------+
| {"Age": 10, "Name": "Bart"} |
+-----------------------------+

So, Bart still wins, and he has been merged with the other members of the first object.

Of course, this also works the other way around – the result is the same if we add the extra member to the second object.

SELECT 
  JSON_MERGE_PATCH('{"Name": "Bartholomew"}', '{"Name": "Bart", "Age": 10}') Result;

Result:

+-----------------------------+
| Result                      |
+-----------------------------+
| {"Age": 10, "Name": "Bart"} |
+-----------------------------+

Example 4 – More Documents

You’re not limited to merging just two documents. You can merge as many as needed. Here’s an example of merging three objects.

SELECT 
  JSON_MERGE_PATCH('{"Name": "Bart"}', '{"Age": 10}', '{"Hair Color": "Yellow"}') Result;

Result:

+-----------------------------------------------------+
| Result                                              |
+-----------------------------------------------------+
| {"Age": 10, "Name": "Bart", "Hair Color": "Yellow"} |
+-----------------------------------------------------+

Example 5 – Arrays

Merging two arrays of the same name will result in only one of them being preserved:

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

Result:

+--------------------------------+
| Result                         |
+--------------------------------+
| {"Hobbies": ["Skateboarding"]} |
+--------------------------------+

Again, you can use JSON_MERGE_PRESERVE() if you need to preserve both arrays. So the previous example can be rewritten to the following:

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

Result:

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

Example 6 – A Larger JSON Document

Here’s an example that merges (slightly) larger JSON documents.

SET @data1 = '{  
    "Suspect": {    
       "Name": "Bart", 
       "Hobbies": ["Skateboarding", "Mischief"]  
    }
 }',
 @data2 = '{  
    "Suspect": {    
       "Age": 10, 
       "Parents": ["Marge", "Homer"]  
    }
 }';
SELECT JSON_MERGE_PATCH(@data1, @data2) Result;

Result:

+-------------------------------------------------------------------------------------------------------------------+
| Result                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------+
| {"Suspect": {"Age": 10, "Name": "Bart", "Hobbies": ["Skateboarding", "Mischief"], "Parents": ["Marge", "Homer"]}} |
+-------------------------------------------------------------------------------------------------------------------+

For the exact rules for how this function performs merges, see the MySQL documentation.