The JSON_MERGE_PRESERVE function in MySQL is used to merge two or more JSON documents while preserving duplicate keys. This means that if there are overlapping keys in the input JSON documents, the function will merge them into a single JSON document, keeping all the values associated with each unique key. If a key appears in multiple input documents, the values associated with that key will be combined into an array.

The JSON_MERGE_PRESERVE function follows these merge rules:

Arrays: Adjacent arrays are merged into a single array.

Objects: Adjacent objects are merged into a single object.

Scalar values: A scalar value is automatically wrapped as an array and merged as an array.

Mixed arrays and objects: Adjacent arrays and objects are merged by automatically wrapping the object as an array and then merging the two arrays.

Syntax

Here is the syntax for the JSON_MERGE_PRESERVE function:

JSON_MERGE_PRESERVE(json_doc1, json_doc2, ...)

json_doc1, json_doc2, …: These are the JSON documents that you want to merge. You can specify two or more JSON documents as arguments.

Example

Now, let’s look at an example to understand how the JSON_MERGE_PRESERVE function works:

-- Example JSON documents
SET @json1 = '{"name": "John", "age": 30, "city": "New York"}';
SET @json2 = '{"age": 31, "city": "San Francisco", "country": "USA"}';
SET @json3 = '{"city": "Los Angeles", "hobbies": ["reading", "traveling"]}';

-- Using JSON_MERGE_PRESERVE to merge the documents
SELECT JSON_MERGE_PRESERVE(@json1, @json2, @json3);

In this example, we have three JSON documents (@json1, @json2, and @json3). The JSON_MERGE_PRESERVE function is used to merge these documents. The resulting merged JSON document will preserve duplicate keys and combine values associated with those keys into arrays when necessary.

The output of the query will be:

{"name": "John", "age": [30, 31], "city": ["New York", "San Francisco", "Los Angeles"], "country": "USA", "hobbies": ["reading", "traveling"]}

As you can see, the age and city keys had duplicate entries in the input JSON documents, so the values are combined into arrays in the merged JSON document. The country key, which only appears in one of the input documents, is included as is in the final result.