You can use Oracle SQL function json_mergepatch to update specific portions of a JSON document. You pass it a JSON Merge Patch document, which specifies the changes to make to a specified JSON document. JSON Merge Patch is an IETF standard.

Function json_mergepatch returns the modified JSON data.

You can use it in an UPDATE statement, to update the documents in a JSON column. Example 12-3 illustrates this.

You can use it in a SELECT list, to modify the selected documents. The modified documents can be returned or processed further. Example 12-4 illustrates this.

Function json_mergepatch can accept as input, and return as output, any SQL data type that supports JSON data: JSON, VARCHAR2, CLOB, or BLOB. Data type JSON is available only if database initialization parameter compatible is 20 or greater.

The default return type depends on the input data type. If the input type is JSON then JSON is also the default return type. Otherwise, VARCHAR2 is the default return type.

JSON Merge Patch is suitable for updating JSON documents that primarily use objects for their structure and do not make use of explicit null values. You cannot use it to add, remove, or change array elements (except by explicitly replacing the whole array). And you cannot use it to set the value of a field to null.

JSON Merge Patch acts a bit like a UNIX patch utility: you give it (1) a source document to patch and (2) a patch document that specifies the changes to make, and it returns a copy of the source document updated (patched). The patch document specifies the differences between the source and the result documents. For UNIX patch the differences are in the form of UNIX diff utility output. For JSON Merge Patch both source and patch are JSON documents.

You can think of JSON Merge Patch as merging the contents of the source and the patch. When merging two objects, one from source and one from patch, a member with a field that is in one object but not in the other is kept in the result. An exception is that a patch member with field value is null is ignored when the source object has no such field.

A little more precisely, JSON Merge Patch acts as follows:

• If the patch is not a JSON object then replace the source by the patch.

• Otherwise (the patch is an object), do the following:

  1. If the source is not an object then act as if it were the empty object ({}).

  2. Iterate over the (p-field:p–value) members of the patch object.

     • If the p-value of the patch member is null then remove the corresponding member from the source.

     • Otherwise, recurse: Replace the value of the corresponding source field with the result of merge-patching that value (as the next source) with the p-value (as the next patch).

If a patch field value of null did not have a special meaning (remove the corresponding source member with that field) then you could use it as a field value to set the corresponding source field value to null. The special removal behavior means you cannot set a source field value to null.

Examples:

• Patch member "PONumber":99999 overrides a source member with field PONumber, replacing its value with the patch-specified value, 99999.

  json_mergepatch('{"User":"ABULL", "PONumber":1600}', '{"PONumber":99999}') results in {"User":"ABULL", "PONumber":99999}.

• Patch member "tracking":123456 overrides a missing source member with field tracking, adding that patch member to the result. And source member "PONumber":1600 overrides a missing patch member with field PONumber — it is kept in the result.

  json_mergepatch('{"PONumber":1600}', '{"tracking":123456}') results in {"PONumber":1600, "tracking":123456}".

• Patch member "Reference":null overrides a source member with field Reference, removing it from the result.

  json_mergepatch('{"PONumber":1600, "Reference":"ABULL-20140421"}', '{"Reference":null}') results in {"PONumber":1600}.

• Patch value [1,2,3] overrides the corresponding source value, [4,5,6], replacing it.

  json_mergepatch('{"PONumber":1600, "LineItems":[1, 2, 3]}', '{"LineItems":[4,5,6]}') results in {"PONumber":1600, "LineItems":[4, 5, 6]}.

{ "Category" : "Platinum",
  "ShippingInstructions" : null,
  "Special Instructions" : "Contact User SBELL",
  "LineItems" : [],
  "AllowPartialShipment" : null,
  "Allow Partial Shipment" : false }

{ "PONumber" : 1600,
  "Reference" : "ABULL-20140421",
  "Requestor" : "Alexis Bull",
  "User" : "ABULL",
  "CostCenter" : "A50",
  "Special Instructions" : "Contact User SBELL",
  "Allow Partial Shipment" : false,
  "LineItems" : [],
  "Category" : "Platinum" }

UPDATE j_purchaseorder SET po_document =
  json_mergepatch(po_document, '{"Special Instructions":null}');

SELECT json_mergepatch(po_document, '{"Special Instructions":null}'
                       RETURNING CLOB PRETTY)
  FROM j_purchaseorder;