Purpose
This function merges two or more JSON documents according to the RFC 7396 standard and returns the merged result. The result does not retain members with duplicate keys. If a document passed as a parameter is invalid, an error is thrown.
Syntax
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
Description
The json_doc parameter specifies the JSON document to be merged.
The rules for executing a merge using JSON_MERGEPatch() are as follows:
If the first parameter is not an object, it is treated as an empty object and merged with the second parameter to produce the result.
If the second parameter is not an object, the result of the merge is the second parameter.
If both parameters are objects, the result of the merge is an object with the following members:
No member of the first object has a corresponding member with the same key in the second object.
All members of the second object do not have corresponding keys in the first object, and their values are not the JSON null literal.
The keys of all members exist in both the first and second objects, and the values in the second object are not JSON null literals. The values of these members are the results of recursively merging the values in the first object with those in the second object.
Examples
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]') |
+------------------------------------------------+
| [true, false] |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}') |
+-------------------------------------------------+
| {"id": 23, "name": "x"} |
+-------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true |
+-------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}') |
+---------------------------------------------+
| {"id": 45} |
+---------------------------------------------+
1 row in set
obclient> 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
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL);
+--------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL) |
+--------------------------------------------+
| NULL |
+--------------------------------------------+
1 row in set
obclient> 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