JSON_MERGE_PRESERVE() – Merge Multiple JSON Documents in MySQL

In MySQL, the JSON_MERGE_PRESERVE() function merges two or more JSON documents and returns the result.

You provide the JSON documents as arguments.

This function was added in MySQL 8.0.3 as a synonym for JSON_MERGE(), however, the JSON_MERGE() function is now deprecated, and is subject to removal in a future release of MySQL.

Syntax

The syntax goes like this:

JSON_MERGE_PRESERVE(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.

If any argument is NULL, this function returns NULL.

Example 1 – Basic Usage

Here’s an example to demonstrate.

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

Result:

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

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

This is exactly the same result we’d get if we used the JSON_MERGE_PATCH() function. However, if we try to merge duplicate keys, these two functions will produce different results.

Example 2 – Duplicate Keys

As the name suggests, the JSON_MERGE_PRESERVE() function preserves members with duplicate keys (this is where it differs from the JSON_MERGE_PATCH() function).

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

Result:

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

So in this case, an array was created and both Bart and Bartholomew were added as separate elements of that array.

This is in contrast to the JSON_MERGE_PATCH() function, which does the following:

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

Result:

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

Example 3 – Multiple Members

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

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

Result:

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

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_PRESERVE('{"Name": "Bartholomew"}', '{"Name": "Bart", "Age": 10}') Result;

Result:

+----------------------------------------------+
| Result                                       |
+----------------------------------------------+
| {"Age": 10, "Name": ["Bartholomew", "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_PRESERVE('{"Name": "Bart"}', '{"Age": 10}', '{"Hair Color": "Yellow"}') Result;

Result:

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

Example 5 – Arrays

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

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

Result:

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

This is another example where JSON_MERGE_PATCH() would return a different result.

Obviously, if the arrays have different names, they’ll end up as separate arrays (but within the same JSON document):

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

Result:

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

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"],
       "Hobbies": ["Trouble"]  
    }
 }';
SELECT JSON_MERGE_PRESERVE(@data1, @data2) Result;

Result:

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

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