top of page
CerebroSQL

MySQL: 

JSON_MERGE_PATCH()

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

Performs an RFC 7396 (https://tools.ietf.org/html/rfc7396) compliant
merge of two or more JSON documents and returns the merged result,
without preserving members having duplicate keys. Raises an error if at
least one of the documents passed as arguments to this function is not
valid.

*Note*:

For an explanation and example of the differences between this function
and JSON_MERGE_PRESERVE(), see
https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.htm
l#json-merge-patch-json-merge-preserve-compared.

JSON_MERGE_PATCH() performs a merge as follows:

1. If the first argument is not an object, the result of the merge 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 result of the merge is
the second argument.

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

o All members of the first object which do not have a corresponding
member with the same key in the second object.

o 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.

o 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.

For additional information, see
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-normalization.

URL: https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

Example

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false] |
+---------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------------+
| {"id": 47, "name": "x"} |
+-------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true |
+-------------------------------+

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
+------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
+------------------------------------------+
| {"id": 47} |
+------------------------------------------+

mysql> 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} |
+-----------------------------------------------------------+

mysql> 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} |
+-------------------------------------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a": 1} |
+--------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
+----------------------------------------------------+
| {"a": {"x": 1, "y": 2}} |
+----------------------------------------------------+

bottom of page