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.