Purpose
JSON_MERGE_PATCH() merges two or more JSON documents based on the standard RFC 7396 format and returns the merged result. Members with duplicate keys are not retained. If an argument passes an invalid document, an error is triggered.
Syntax
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
Description
json_doc specifies the JSON documents to be merged.
JSON_MERGE_PATCH() performs the merge based on the following rules:
If the first argument is not an object, it is used as an empty object and merged with the second argument.
If the second argument is not an object, the merge result is the second argument.
If both arguments are objects, the merge produces an object that has the following members:
All members of the first object that do not have a corresponding member with the same key in the second object.
All members of the second object which do not have a corresponding key in the first object, and whose value is not the JSON null literal.
All members with a key that exists in both the first and the second object, and whose value in the second object is not the JSON null literal. The values of these members are the results of recursively merging the value in the first object with the value 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