Home > JSON, PL/SQL, SQL > JSON Patch, Merge Patch, and Redact features in Oracle 18c

JSON Patch, Merge Patch, and Redact features in Oracle 18c

These days, I am looking into the latest additions to SODA in Oracle Database 18c, in particular the REST implementation available via Oracle REST Data Services (ORDS) : SODA for REST.

I wrote once about SODA in the past (see here), to give a preview of JSON filter expressions before their official documentation in release 12.2.

Before looking more closely at the features mentioned in the title, I’ll set up a little SODA collection.

A collection of documents created and managed via SODA is backed up by a database table containing at least an “id” column and a “content” column to store JSON documents.
Though Oracle 18c introduced SODA for PL/SQL as well, I’ll use the HTTP protocol to set up the following example.
 

Creating a new SODA collection

Without custom metadata, documents of a SODA collection are stored in a BLOB column by default.
For subsequent ease-of-use, I’ll pass the following metadata to create the table with the minimum structure : a numeric “ID” column based on a sequence, and a “DOC” column of CLOB data type.

collMetadata.json

{
  "tableName": "JSON_DOCUMENTS",
  "keyColumn" : {
    "name": "ID",
    "sqlType": "NUMBER",
    "assignmentMethod": "SEQUENCE",
    "sequenceName": "JSON_DOC_SEQ"
  },
  "contentColumn": {
    "name": "DOC",
    "sqlType": "CLOB",
    "cache": true
  }
}
Creating the sequence :
create sequence json_doc_seq;

 

Creating the collection, using a curl command :
curl -i -X PUT --data-binary @collMetadata.json -H "Content-Type: application/json" 
http://localhost:8083/ords/dev/soda/latest/MyCollection
SQL> desc json_documents

 Name    Null?    Type
 ------- -------- -------------
 ID      NOT NULL NUMBER
 DOC              CLOB

 

Inserting documents :

I’ll load the ready-to-use sample file ‘POList.json’ available in the ./examples/soda/getting-started directory of ORDS installation.
This file holds a JSON array containing 70 PO documents.

curl -X POST --data-binary @POList.json -H "Content-Type: application/json" 
http://localhost:8083/ords/dev/soda/latest/custom-actions/insert/MyCollection

Here’s the content of the table after successful execution :

SQL> select * from json_documents order by id;

        ID DOC
---------- ----------------------------------------------------------------------------
         1 {"PONumber":1,"Reference":"MSULLIVA-20141102","Requestor":"Martha Sullivan",
         2 {"PONumber":2,"Reference":"MSULLIVA(-20141113","Requestor":"Martha Sullivan"
         3 {"PONumber":3,"Reference":"TRAJS-20140518","Requestor":"Trenna Rajs","User":
         4 {"PONumber":4,"Reference":"TRAJS-20140520","Requestor":"Trenna Rajs","User":
         5 {"PONumber":5,"Reference":"MSULLIVA-20141121","Requestor":"Martha Sullivan",
         6 {"PONumber":6,"Reference":"TRAJS-20140530","Requestor":"Trenna Rajs","User":
         7 {"PONumber":7,"Reference":"VJONES-20140503","Requestor":"Vance Jones","User"
...
        66 {"PONumber":66,"Reference":"SMCCAIN-20141007","Requestor":"Samuel McCain","U
        67 {"PONumber":67,"Reference":"SMCCAIN-20141010","Requestor":"Samuel McCain","U
        68 {"PONumber":68,"Reference":"SHIGGINS-20141028","Requestor":"Shelley Higgins"
        69 {"PONumber":69,"Reference":"KPARTNER-20140905","Requestor":"Karen Partners",
        70 {"PONumber":70,"Reference":"SSTILES-20141011","Requestor":"Stephen Stiles","

70 rows selected.

 
The setup is complete.
Let’s now try out the new PATCH operation.
 

JSON PATCH operation

PATCH is a standard request method of the HTTP protocol, whose purpose is to make piecewise changes to a resource (a JSON document in this case).
The SODA HTTP PATCH operation implements the JSON Patch standard as per RFC 6902.
A JSON Patch specification is itself a JSON document representing an array of Patch steps. For example :

poPatchSpec.json

[
  { "op"    : "test",
    "path"  : "/ShippingInstructions/Address/street",
    "value" : "200 Sporting Green" },
  { "op"    : "replace",
    "path"  : "/ShippingInstructions/Address/street",
    "value" : "Winchester House, Heatley Rd" },
  { "op"    : "copy",
    "from"  : "/ShippingInstructions/Phone/0",
    "path"  : "/ShippingInstructions/Phone/1" },
  { "op"    : "replace",
    "path"  : "/ShippingInstructions/Phone/1/number",
    "value" : "861-555-8765" }
]

– First step is a “test” operation, which acts as a filter predicate.
It simply checks whether the value at location '/ShippingInstructions/Address/street' is '200 Sporting Green'. If not, the processing stops, else continues with the next steps.

– Second step is a “replace” operation.
It replaces the value at '/ShippingInstructions/Address/street' with value 'Winchester House, Heatley Rd'.

– Third step is a “copy”” operation.
It copies the value at location '/ShippingInstructions/Phone/0' (an array location) to the target location '/ShippingInstructions/Phone/1'.

– Fourth step is another “replace” operation, this time targetting the ‘number’ field of the new Phone item added on the previous step.

Path expressions used in JSON Patch are defined in RFC 6901 (JSON Pointer).

Here’s the relevant data for doc ID = 1 before applying the patch spec :

SQL> select po.*
  2  from json_documents t
  3     , json_table(t.doc, '$.ShippingInstructions'
  4         columns street varchar2(30)  path '$.Address.street'
  5               , phone  varchar2(256) format json with array wrapper path '$.Phone[*].number'
  6       ) po
  7  where t.id = 1;

STREET                         PHONE
------------------------------ --------------------
200 Sporting Green             ["979-555-6598"]
 

Applying patch :

curl -i -X PATCH --data-binary @poPatchSpec.json -H "Content-Type: application/json-patch+json" 
http://localhost:8083/ords/dev/soda/latest/MyCollection/1

Data after patch operation :

SQL> select po.*
  2  from json_documents t
  3     , json_table(t.doc, '$.ShippingInstructions'
  4         columns street varchar2(30)  path '$.Address.street'
  5               , phone  varchar2(256) format json with array wrapper path '$.Phone[*].number'
  6       ) po
  7  where t.id = 1;

STREET                         PHONE
------------------------------ -----------------------------------
Winchester House, Heatley Rd   ["979-555-6598","861-555-8765"]
 

 
So what happened behind the scenes?
We can check that using SQL_Trace, or by just querying V$SQL (before cursors age out of the shared pool).
Here are my findings :

select DBMS_SODA_DOM.JSON_PATCH_C("DOC",:1 ),to_char("ID") from "DEV"."JSON_DOCUMENTS" where ("ID" = to_number(:2 ) )

update "DEV"."JSON_DOCUMENTS" set "DOC" = :1  where ("ID" = to_number(:2 ) )

First statement selects the document and apply the patch using DBMS_SODA_DOM.JSON_PATCH_C function.
Second statement saves back the document in the collection table.

DBMS_SODA_DOM is not documented. It contains various functions supporting – among other interesting things – the PATCH method against different data types (VARCHAR2, NVARCHAR2, RAW, CLOB, NCLOB, BLOB).

Pushing the analysis a bit further, we can discover that those PATCH routines are actually based on PL/SQL JSON_ELEMENT_T.patch() method :

MEMBER PROCEDURE patch(self IN OUT NOCOPY JSON_ELEMENT_T, spec VARCHAR2)

 
Let’s now experiment with this method directly from PL/SQL.
 

Disclaimer :
The patch method is not yet documented, so “unsupported” for direct usage.
All the following is then presented for informational purpose.

 

JSON Patch : PL/SQL Examples

I’ll first create a PL/SQL function to be able to run tests from SQL context :

create or replace function my_json_patch (
  doc  in clob
, spec in varchar2
)
return varchar2
is
  elem  json_element_t := json_element_t.parse(doc);
begin
  elem.patch(spec);
  return elem.to_string();
end;
/

And insert a small sample document into the collection (using SODA for PL/SQL) :

declare
  coll  soda_collection_t := dbms_soda.open_Collection('MyCollection');
  doc   soda_document_t := soda_document_t(
                             c_content => '{ "text": "Hello", "list": [1, 2, 3],"type": { "valid": true, "value": 1 } }'
                           );
begin
  doc := coll.insert_one_and_get(doc);
  dbms_output.put_line('New key = '||doc.get_key);
  commit;
end;
/

New key = 123

 
Add object member

select my_json_patch(doc
       , '[{ "op": "add", "path": "/foo", "value": "bar" }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"valid":true,"value":1},"foo":"bar"}

 
Add array item (at first position)

select my_json_patch(doc
       , '[{ "op": "add", "path": "/list/0", "value": 0 }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[0,1,2,3],"type":{"valid":true,"value":1}}

 
Append an array item

select my_json_patch(doc
       , '[{ "op": "add", "path": "/list/-", "value": 4 }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3,4],"type":{"valid":true,"value":1}}

 
Remove object member

select my_json_patch(doc
       , '[{ "op": "remove", "path": "/type" }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3]}

 
Remove array item (second one)

select my_json_patch(doc
       , '[{ "op": "remove", "path": "/list/1" }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,3],"type":{"valid":true,"value":1}}

 
Remove last array item

select my_json_patch(doc
       , '[{ "op": "remove", "path": "/list/-" }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2],"type":{"valid":true,"value":1}}

 
Replace value

select my_json_patch(doc
       , '[{ "op": "replace", "path": "/text", "value":"Hello World!" }]'
       )
from json_documents
where id = 123;

{"list":[1,2,3],"type":{"valid":true,"value":1},"text":"Hello World!"}

 
Replace value in array

select my_json_patch(doc
       , '[{ "op": "replace", "path": "/list/0", "value":9 }]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[9,2,3],"type":{"valid":true,"value":1}}

 
Move value

select my_json_patch(doc
       , '[{ "op": "move", "from":"/type/valid" , "path": "/foo"}]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"value":1},"foo":true}

 
Copy value, then replace

select my_json_patch(doc
       , '[{"op": "copy", "from":"/type", "path": "/type2"},
           {"op": "replace", "path": "/type2/valid", "value":false}]'
       )
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"valid":true,"value":1},"type2":{"value":1,"valid":false}}

 
Test value

select my_json_patch(doc
       , '[{"op": "test", "path":"/text", "value": "Hello!"}]'
       )
from json_documents
where id = 123;

ORA-40567: JSON patch operation failed.

 

JSON Merge Patch

JSON Merge Patch is another new and “hidden” feature in Oracle 18c.
Contrary to the Patch operation, there’s no documented SODA interface for Merge Patch, so most of what I’ll say here must be qualified as “educated guess”.

JSON Merge Patch (MP) is standardized in RFC 7396.
MP is similar to Patch in the sense that it describes a set of changes to be made to a target document, but it differs in the format of the specification.
A MP specification is a JSON document whose structure resembles that of the target document, but only includes (nested) objects and members to be modified, added or removed.

The PL/SQL implementation is available in JSON_ELEMENT_T :

MEMBER PROCEDURE mergepatch(self IN OUT NOCOPY JSON_ELEMENT_T, patch VARCHAR2)

 

Examples :

create or replace function my_json_mergepatch (
  doc  in clob
, spec in varchar2
) 
return varchar2 
is
  elem  json_element_t := json_element_t.parse(doc);
begin
  elem.mergepatch(spec);
  return elem.to_string();
end;
/

 

Replacing value of “text” member :

select my_json_mergepatch(doc, '{ "text": "Hello World!" }')
from json_documents
where id = 123;

{"text":"Hello World!","list":[1,2,3],"type":{"valid":true,"value":1}}

 

Removing “type” member :

select my_json_mergepatch(doc, '{ "type": null }')
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3]}

 

Replacing value of “type.value” member :

select my_json_mergepatch(doc, '{ "type": { "value": 2 } }')
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"valid":true,"value":2}}

 

Adding a new member :

select my_json_mergepatch(doc, '{ "foo": "bar" }')
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"valid":true,"value":1},"foo":"bar"}

 

JSON Redact

JSON Redact implements “SELECT”-like projection over JSON data.
Like Patch and Merge Patch, Redact method is available in JSON_ELEMENT_T :

MEMBER PROCEDURE redact(self IN OUT NOCOPY JSON_ELEMENT_T, spec VARCHAR2)

 

The Redact specification is very simple, we just have to list the members we want to include or exclude in the resulting document : 1 or (true) to include, and 0 (or false) to exclude, e.g.

{ "text": true }

We cannot mix include and exclude specifications.

Examples :

create or replace function my_json_redact (
  doc  in clob
, spec in varchar2
) 
return varchar2 
is
  elem  json_element_t := json_element_t.parse(doc);
begin
  elem.redact(spec);
  return elem.to_string();
end;
/

 

Projecting only the “text” member :

select my_json_redact(doc, '{ "text": true }')
from json_documents
where id = 123;

{"text":"Hello"}

 

Projecting both “text” and “list” members :

select my_json_redact(doc, '{ "text": 1, "list": 1 }')
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3]}

 

Excluding “type.value” member :

select my_json_redact(doc, '{ "type.value": 0 }')
from json_documents
where id = 123;

{"text":"Hello","list":[1,2,3],"type":{"valid":true}}

 

Afterword

We have seen various new features related to fine-grained manipulation of JSON documents.
They are still undocumented (or partially documented), but hopefully they will be in the near future.

Note : JSON_ELEMENT_T.Patch and .Redact methods are available on release 12.2 as well.
 

Advertisements
Categories: JSON, PL/SQL, SQL Tags: , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: