Changing a few fields of a JSON document is a common task: upgrade a tier, remove a preference, add a flag. JSON_MERGEPATCH applies a merge patch, as defined by RFC 7386: a small JSON document whose fields replace those of the target, and whose null fields delete them. It is the simplest way to update whole fields, and the patch is itself readable JSON.
Code for This Guide
The main example is in the examples/json folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
json_mergepatch(target, patch [returning type] [pretty])
The rules of a merge patch:
- A field in the patch replaces the field of the same name in the target, or adds it if missing.
- A field whose value is null in the patch deletes that field from the target.
- An object in the patch is merged recursively into the object of the same name.
- Any other value, including an array, replaces the target's value as a whole.
Patch a Loyalty Profile
This patch upgrades a customer's tier, deletes the meal preference inside the nested preferences object, and adds a vip flag. RETURNING and PRETTY shape the output.
Example:
select json_mergepatch(loyalty, '{"tier":"Platinum","preferences":{"meal":null},"vip":true}'
returning varchar2(400) pretty) as patched
from customers
where customer_id = 1;Output:
PATCHED
__________________________________
{
"memberId" : "NM100037",
"tier" : "Platinum",
"points" : 3296,
"memberSince" : "2021-08-17",
"preferences" :
{
"seat" : "aisle",
"newsletter" : true
},
"favoriteAirports" :
[
"CDG",
"JNB",
"NBO"
],
"vip" : true
}The other fields of preferences, seat and newsletter, are kept, because the nested object was merged rather than replaced.
Arrays Are Replaced
A merge patch cannot add one element to an array; it replaces the whole array.
Example:
-- a merge patch replaces an array as a whole, and adds or deletes object fields
select json_mergepatch('{"tier":"Gold","favoriteAirports":["CDG","JNB"],"meal":"veg"}',
'{"favoriteAirports":["DXB"],"meal":null,"seat":"window"}') as patched
from dual;Output:
PATCHED
_____________________________________________________________
{"tier":"Gold","favoriteAirports":["DXB"],"seat":"window"}To append to an array, or to change a single element, use JSON_TRANSFORM, whose APPEND and SET operations work on parts of arrays.
Update Stored Documents
In an UPDATE, JSON_MERGEPATCH changes the stored document: update customers set loyalty = json_mergepatch(loyalty, '{"tier":"Platinum"}') where customer_id = 1. Because null means delete, a patch cannot set a field to the JSON value null; use JSON_TRANSFORM for that too.
Related Guides
Conclusion
JSON_MERGEPATCH applies an RFC 7386 merge patch: fields replace or add, null fields delete, nested objects merge, and arrays are replaced whole. Use it for simple field updates, and JSON_TRANSFORM when you need to change array elements or set nulls.
