Purpose
The JSON_MERGEPATCH() function updates specific parts of the target_json data according to the standard of RFC 7396, merging two or more JSON documents and 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 merge result is the same as combining the empty object with the second parameter.
- If the second parameter is not an object, the merge result is the second parameter.
- If both parameters are objects, the merge 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, provided their values are not JSON null.
- All members of the second object whose keys exist in the first object, provided their values are not JSON null.
- These members' values are recursively merged from the values in the first object and the second object.
Syntax
JSON_MERGEPATCH (
target_expr,
patch_expr
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[PRETTY]
[ASCII]
[TRUNCATE]
[ERROR|NULL ON ERROR]);
Parameters
The JSON_MERGEPATCH() function has the following parameters:
target_expr: The target JSON object.patch_expr: The patch JSON object.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: The data type of the return value, whereVARCHAR2[size]specifies the maximum length of the return value.PRETTY: Whether the output string is pretty-printed when the return value is a character string.- 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 according to the return type.ERRORclause:NULL ON ERROR: Returns null if an error occurs. This is the default behavior.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, the values 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