Purpose
The JSON_MERGEPATCH() function updates a specific part of the target_json data by merging two or more JSON documents according to the standards of RFC 7396 and returns the merged result without retaining members with duplicate keys. If at least one of the documents passed as a parameter to this function is invalid, an error will be raised.
The JSON_MERGEPATCH() function merges 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 syntax of the JSON_MERGEPATCH() function is described 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. TheVARCHAR2[size]option can limit the length of the return value.PRETTY: specifies whether to use pretty-print when returning character types.- ASCII: automatically escapes any non-ASCII Unicode characters in the returned string using standard ASCII Unicode escape sequences.
TRUNCATE: specifies whether the returned text should be truncated based on the return type.ERRORclause:NULL ON ERROR: returns NULL if an error occurs. This 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
# The same key 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
# The TRUNCATE clause takes effect.
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;
ORA-40441: JSON syntax error
# By default, errors are returned as 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