MySQL JSON_MERGE

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

Introduction to MySQL JSON_MERGE_PATCH() function

The JSON_MERGE_PATCH() function merges two or more JSON documents into a single JSON document.

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

JSON_MERGE_PATCH(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 JSON_MERGE_PATCH() function returns a merged JSON document while discarding duplicate keys.

The JSON_MERGE_PATCH() function raises an error if at least one of the JSON documents passed as arguments is invalid.

Note that the JSON_MERGE_PATCH() function performs a merge operation that follows the RFC 7396 standard.

The JSON_MERGE_PATCH() function uses the following rules for merging JSON documents:

1) If the first argument is not an object, the result is the same as if an empty object had been merged with the second argument.

2) If the second argument is not an object, the function returns the second argument.

3) If both arguments are objects, the function returns a JSON object with the following members:

All members from the first object without corresponding members in the second object.
All members from the second object without corresponding keys in the first object, excluding those with a value of JSON null.
Members with keys that exist in both the first and second objects, where the value in the second object is not JSON null. The values result from recursively merging the corresponding values in the first and second objects.

The MySQL JSON_MERGE_PATCH() function examples

Let’s look at some examples to understand how the JSON_MERGE_PATCH() function works.

1) The first argument is not an object

The following example uses the JSON_MERGE_PATCH() function to merge an array with an object:

SELECT 
  JSON_MERGE_PATCH('[1,2]', '{"name": "John Doe"}');Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------------------------+
| JSON_MERGE_PATCH('[1,2]','{"name": "John Doe"}') |
+--------------------------------------------------+
| {"name": "John Doe"}                             |
+--------------------------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) The second argument is not an object

The following example uses the JSON_MERGE_PATCH() function to merge an object with an array, which results in the array:

SELECT 
  JSON_MERGE_PATCH('{"name": "John Doe"}', '[1,2]');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "John Doe"}', '[1,2]') |
+---------------------------------------------------+
| [1, 2]                                            |
+---------------------------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

3) Both arguments are objects

The following example uses the JSON_MERGE_PATCH() function to merge two objects with different keys. The result JSON document is an object that contains all the keys of the merging objects:

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

Output:

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

4) Both arguments are objects, the first object has a key with null

The following example uses the JSON_MERGE_PATCH() function to merge two objects. Both objects have the same key age but the first object has a value of null while the second one has a non-null value:

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

Output:

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

5) Removing a member

The following example also uses the JSON_MERGE_PATCH() function to remove a member of the object in the first JSON document by specifying a null value for the object in the second argument:

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

Output:

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

6) Recursive Merging

The following example also uses the JSON_MERGE_PATCH() function to merge nested JSON documents:

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

Output:

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

Summary

Use the JSON_MERGE_PATCH() function to perform an RFC 7396 compliant merge of two or more JSON documents, returning a merged JSON document while discarding duplicate keys.

Was this tutorial helpful?