Purpose
JSON_MERGEPATCH() updates a specific part of the target_json data, merges two or more JSON documents based on the RFC 7396 standard, and returns the merge result without retaining members with duplicate keys. An error occurs if at least one document passed to this function as an argument is invalid.
The merge principle of JSON_MERGEPATCH() is as follows:
- If the first argument is not an object, the merge result is the same as that of an empty object and the second argument.
- If the second argument is not an object, the merge result is the second argument.
- If both arguments are objects, the merge produces an object that has the following members:
- All members of the first object that do not have a corresponding member with the same key in the second object.
- All members of the second object that do not have a corresponding key in the first object, and whose value is not the JSON NULL literal.
- 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.
Syntax
JSON_MERGEPATCH (
target_expr,
patch_expr
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[PRETTY]
[ASCII]
[TRUNCATE]
[ERROR|NULL ON ERROR]);
Parameters
The parameters in the syntax are described as follows:
target_expr: specifies the target JSON object.patch_expr: specifies the patch JSON object.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: specifies the return value type.VARCHAR2[size]specifies the length of the return value.PRETTY: specifies whether to use pretty-print for returning character data types.ASCII: specifies to automatically escape any non-ASCII Unicode characters in the returned string by using standard ASCII Unicode escape sequences.TRUNCATE: specifies that the returned text will be truncated based on the return type.ERROR:NULL ON ERROR: specifies to return NULL when an error occurs. This is the default setting.ERROR ON ERROR: specifies to return the error code when an error occurs.
Examples
# Use default arguments.
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, the data is deleted.
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 data is 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;
ORA-40441: JSON syntax error
# Set the function to return NULL by default when an error occurs.
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