Purpose
The JSON_MERGEPATCH() function updates specific parts of the target_json data by merging two or more JSON documents according to the RFC 7396 standard, returning the merged result without retaining members with duplicate keys. If at least one document passed as a parameter to this function is invalid, an error will be raised.
The JSON_MERGEPATCH() function merges the documents as follows:
- If the first parameter is not an object, the merged result is the same as merging an empty object with the second parameter.
- If the second parameter is not an object, the merged result is the second parameter.
- If both parameters are objects, the merged 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 have keys present in both the first and second objects, and their 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.
Syntax
JSON_MERGEPATCH (
target_expr,
patch_expr
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[PRETTY]
[ASCII]
[TRUNCATE]
[ERROR|NULL ON ERROR]);
Syntax
The JSON_MERGEPATCH() function syntax is as follows:
target_expr: the target JSON object.patch_expr: the patch JSON object.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: specifies the return value type, whereVARCHAR2[size]can limit the length of the return value.PRETTY: specifies whether to use pretty-print formatting for the output string when returning a character type.- ASCII: specifies to automatically escape any non-ASCII Unicode characters in the returned string using standard ASCII Unicode escape sequences.
TRUNCATE: specifies that the returned text should be truncated according to the return type.ERRORclause:NULL ON ERROR: returns NULL if an error occurs, which is the default setting.ERROR ON ERROR: returns the error code.
Examples
# Use the default parameters.
obclient> SELECT json_mergepatch('{"a":"b"}', '{"b":"c"}') FROM DUAL;
+------------------------------------------+
| JSON_MERGEPATCH('{"A":"B"}','{"B":"C"}') |
+------------------------------------------+
| {"a": "b", "b": "c"} |
+------------------------------------------+
1 row in set
# When the patch is null, it is equivalent to deleting.
obclient> SELECT json_mergepatch('{"a":"b"}', '{"a":null}') FROM DUAL;
+-------------------------------------------+
| JSON_MERGEPATCH('{"A":"B"}','{"A":NULL}') |
+-------------------------------------------+
| {} |
+-------------------------------------------+
1 row in set
# When the keys are the same, it will be updated.
obclient> SELECT json_mergepatch('{"a":["b"]}', '{"a":"c"}') FROM DUAL;
+--------------------------------------------+
| JSON_MERGEPATCH('{"A":["B"]}','{"A":"C"}') |
+--------------------------------------------+
| {"a": "c"} |
+--------------------------------------------+
1 row in set
# Use the RETURNING and PRETTY clauses.
obclient> SELECT json_mergepatch('{"a":"b", "b":"c"}', '{"a":null}' RETURNING CLOB PRETTY ) FROM DUAL;
+----------------------------------------------------------------------+
| JSON_MERGEPATCH('{"A":"B","B":"C"}','{"A":NULL}'RETURNINGCLOBPRETTY) |
+----------------------------------------------------------------------+
| {
"b": "c"
} |
+----------------------------------------------------------------------+
1 row in set
# Use the TRUNCATE clause.
obclient> SELECT json_mergepatch(
'{"a":"b"}',
'{"a":"cccccccccccccccccccccccccccccbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbccccccccccccccccccccccccccccccccccccccccccccccccc"}'
RETURNING varchar2(32) PRETTY TRUNCATE) FROM DUAL;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_MERGEPATCH('{"A":"B"}','{"A":"CCCCCCCCCCCCCCCCCCCCCCCCCCCCCBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC"}'RETURNINGVARCHAR2(32)PRETTYTRUNCATE) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"a": "cccccccccccccccccccccc |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
# Use the ERROR clause.
obclient> SELECT json_mergepatch('{', '[1,2,3]' ERROR ON ERROR) FROM DUAL;
OBE-40441: JSON syntax error
# By default, errors return NULL.
obclient> SELECT json_mergepatch('{', '[1,2,3]') FROM DUAL;
+--------------------------------+
| JSON_MERGEPATCH('{','[1,2,3]') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set
# Specify the ERROR clause
obclient> SELECT json_mergepatch('{', '[1,2,3]' NULL ON ERROR) FROM DUAL;
+-------------------------------------------+
| JSON_MERGEPATCH('{','[1,2,3]'NULLONERROR) |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set