MySQL JSON_MERGE_PRESERVE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_MERGE_PRESERVE() function to merge two or more JSON documents.

Introduction to MySQL JSON_MERGE_PRESERVE() function

The JSON_MERGE_PRESERVE() function merges two or more JSON documents and returns a merged JSON document while preserving the structure of the original JSON document.

Here’s the syntax of the JSON_MERGE_PRESERVE() function:

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document that you want to merge. The function accepts two or more json_doc arguments.

The function returns a merged JSON document. It returns NULL if any argument is NULL.

If any argument is not a valid JSON document, the function raises an error.

The function uses the following rules for merging JSON documents while preserving the structure of the original JSON document:

• Adjacent arrays are merged into a single array.
• Adjacent objects are merged into a single object.
• A scalar value is automatically wrapped as an array and merged as an array.
• Adjacent arrays and objects are merged by automatically wrapping the object as an array and then merging the two arrays.

To merge two or more JSON documents into a JSON document while discarding duplicate keys, you use the JSON_MERGE_PATCH() function.

MySQL JSON_MERGE_PRESERVE() function examples

Let’s look at some examples to understand how JSON_MERGE_PRESERVE() works.

1) Merging arrays into a single array

The following example uses the JSON_MERGE_PRESERVE() function to merge two arrays into a single array:

SELECT 
  JSON_MERGE_PRESERVE('[1, 2]', '[3, 4]') result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------+
| result       |
+--------------+
| [1, 2, 3, 4] |
+--------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Merging objects into a single object

The following example uses the JSON_MERGE_PRESERVE() function to merge two objects into a single object:

SELECT 
  JSON_MERGE_PRESERVE('{"name": "John Doe"}', '{"age": 25}') result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------+
| result                          |
+---------------------------------+
| {"age": 25, "name": "John Doe"} |
+---------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Merging scalars into an array

The following example uses the JSON_MERGE_PRESERVE() function to merge two scalar values into an array whose elements are the scalar values:

SELECT 
  JSON_MERGE_PRESERVE('1', 'true') result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| result    |
+-----------+
| [1, true] |
+-----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Merging an arrays and an object into an array

The following example uses the JSON_MERGE_PRESERVE() function to merge an array with an object and return an array that contains the object as the last element:

SELECT 
  JSON_MERGE_PRESERVE(
    '["Jane", "John"]', '{"name": "Alice"}'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------+
| result                              |
+-------------------------------------+
| ["Jane", "John", {"name": "Alice"}] |
+-------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) Merging objects with the same keys

The following example uses the JSON_MERGE_PRESERVE() function to merge two objects with the same key and return an object with the values are the arrays containing the values of each object.

SELECT 
  JSON_MERGE_PRESERVE(
    '{ "name": "Jane Doe", "age": 24 }', 
    '{ "name": "Jane Smith", "age": 25 }'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------------------------+
| result                                                |
+-------------------------------------------------------+
| {"age": [24, 25], "name": ["Jane Doe", "Jane Smith"]} |
+-------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_MERGE_PRESERVE() function to merge two or more JSON documents while preserving the structure of the original JSON document.