Purpose
The JSON_MERGE_PATCH() function merges two or more JSON documents based on RFC 7396 and returns the result. The result does not retain members with duplicate keys. If an invalid JSON document is passed as a parameter, an error is raised.
Syntax
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
Purpose
The json_doc parameter specifies the JSON documents to be merged.
The merging rules for JSON_MERGE_PATCH() are as follows:
If the first parameter is not an object, it is treated as an empty object and merged with the second parameter.
If the second parameter is not an object, the result is the second parameter.
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 objects and whose values in the second object are not JSON null literals. The values of these members are the result of recursively merging the values from the first object with those from 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