JSON_MERGE_PATCH

Description

This function merges two or more JSON documents based on the RFC 7396 standard and returns the merged result. The result does not retain members with duplicate keys. If any of the provided documents are invalid, an error is raised.

Syntax

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

Description

The json_doc parameter specifies the JSON documents to be merged.

The JSON_MERGE_PATCH() function follows these rules for merging:

1. If the first parameter is not an object, it is treated as an empty object merged with the second parameter as the result.

2. If the second parameter is not an object, the result is the second parameter.

3. If both parameters are objects, the result is an object with the following members:

   • All members of the first object that do not have corresponding members with the same key in the second object.

   • All members of the second object that do not have corresponding keys in the first object and whose values are not JSON null literals.

   • All members that exist in both the first and second objects, where the value in the second object is not a JSON null literal. The values of these members are recursively merged from the first and second objects.

Examples

SELECT JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]') |
+------------------------------------------------+
| [true, false]                                  |
+------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}') |
+-------------------------------------------------+
| {"id": 23, "name": "x"}                         |
+-------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}') |
+---------------------------------------------+
| {"id": 45}                                  |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+-----------------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                                  |
+-----------------------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL);
+--------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL) |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
+--------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+--------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                               |
+--------------------------------------------------------------------------------+
1 row in set (0.001 sec)