Purpose
The JSON_MERGEPATCH() function updates a specific part of the target_json data. It follows RFC 7396 to merge two or more JSON documents and returns the merged result, omitting members that have duplicate keys. If at least one document passed as an argument to this function is invalid, an error is raised.
The JSON_MERGEPATCH() function performs the following merge operation:
- If the first argument is not an object, the result of merging with the second argument is the same as merging the empty object with the second argument.
- If the second argument is not an object, then the result of the merge is the second argument.
- If both arguments are objects, the merged result is an object with the following members:
- The members of the first object do not have corresponding members in the second object with the same keys.
- The second object has members that are not present in the first object and are not JSON null values.
- The keys of all the members exist in the first and second object, and their values in the second object are not JSON Null values.
- The values for these members are the result of recursively merging values from the first object into the second.
Syntax
JSON_MERGEPATCH (
target_expr,
patch_expr
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[PRETTY]
[ASCII]
[TRUNCATE]
[ERROR|NULL ON ERROR]);
Syntax
The syntax for the JSON_MERGEPATCH() function is described as follows:
target_expr: The target JSON object.patch_expr: A JSON object containing the patch expressions.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: This clause specifies the return type.VARCHAR2[size]specifies the maximum return value length.PRETTY: specifies whether to use pretty-print output for the returned character data.- ASCII: Use the standard ASCII Unicode escape sequence to automatically escape any non-ASCII Unicode character in the returned string.
TRUNCATE: A clause that specifies that the text returned should be truncated according to the return type.ERRORclause:NULL ON ERROR: Returns NULL when an error occurs. This is the default setting.ERROR ON ERROR: The error code is returned.
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 value 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;
OBE-40441: JSON syntax error
# By default, returns NULL 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