How to Publish JSON Duality Views through ORDS

JSON relational duality views present relational rows as JSON documents that applications can read and write, with optimistic locking built in. ORDS publishes them like any view with AutoREST, so a document API needs no handler code. This guide publishes a duality view of routes, updates a document, and shows the built-in conflict check.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

Syntax

create json relational duality view view_name as
table @update            -- allowed operations: @insert, @update, @delete
{ _id : primary_key, field : column, ... };

ords.enable_object(p_object => 'VIEW_NAME', p_object_type => 'VIEW',
                   p_object_alias => 'alias', ...);

A Duality View of Routes

ROUTE_DV shows each route as a document. It allows updates only, and only blockMinutes is updatable, so the API cannot add or remove routes or change their airports.

Example:

create or replace json relational duality view route_dv as
routes @update
{
  _id          : route_id,
  origin       : origin       @noupdate,
  destination  : destination  @noupdate,
  distanceKm   : distance_km  @noupdate,
  blockMinutes : block_minutes
};

begin
  ords.enable_object(p_enabled        => true,
                     p_schema         => 'NIMBUS',
                     p_object         => 'ROUTE_DV',
                     p_object_type    => 'VIEW',
                     p_object_alias   => 'route_docs',
                     p_auto_rest_auth => false);
  commit;
end;
/

Output:

View ROUTE_DV created.

PL/SQL procedure successfully completed.

Read a Document

Example:

curl -k https://localhost:8443/ords/nimbus/route_docs/7

Output:

{
    "_id": 7,
    "origin": "DXB",
    "destination": "AMS",
    "distanceKm": 5168,
    "blockMinutes": 378,
    "_metadata": {
        "etag": "CA9464B082EF18FD5613AE62A2318CCA",
        "asof": "00000000011CBB5C"
    },
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/route_docs/7"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/route_docs/item"
        },
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/nimbus/route_docs/"
        }
    ]
}

The _metadata etag identifies this version of the document.

Update a Document

The client sends the whole document back with a changed blockMinutes and the etag it read:

Example:

{"_id":7,"origin":"DXB","destination":"AMS","distanceKm":5168,"blockMinutes":385,"_metadata":{"etag":"CA9464B082EF18FD5613AE62A2318CCA"}}

Example:

curl -k -X PUT https://localhost:8443/ords/nimbus/route_docs/7 \
  -H "Content-Type: application/json" --data-binary @put-body.json

Output (HTTP 200 OK, start of the response):

{
    "_id": 7,
    "origin": "DXB",
    "destination": "AMS",
    "distanceKm": 5168,
    "blockMinutes": 385,
    "_metadata": {
        "etag": "8F4C6634FA600536CE42618FB927EDFA",
        "asof": "00000000011CBB63"
    },
    "links": [
        {
    ...

The update succeeded, and the document has a new etag.

A Stale Document Is Refused

Sending the same body again, with the old etag, means the client did not see the latest version:

Output (HTTP 412 Precondition Failed):

{
    "code": "PredconditionFailed",
    "message": "Predcondition Failed",
    "type": "tag:oracle.com,2020:error/PredconditionFailed",
    "instance": "tag:oracle.com,2020:ecid/WE01x10M0XEsNRZ9AW2AKQ"
}

The spelling PredconditionFailed is exactly what ORDS returned. Inserting a document fails as well, since the view allows no @insert:

Output (HTTP 400 Bad Request):

{
    "code": "BadRequest",
    "title": "Bad Request",
    "message": "The request could not be processed for a user defined resource",
    "o:errorCode": "ORDS-25001",
    "action": "Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource",
    ...

A document that changes a field marked @noupdate, such as distanceKm, is rejected too, and the table keeps its value:

Output (HTTP 400 Bad Request):

{
    "code": "BadRequest",
    "title": "Bad Request",
    "message": "The request could not be processed for a user defined resource",
    "o:errorCode": "ORDS-25001",
    ...

After the test, route 7 was set back to 378 minutes with another PUT.

Filter Documents

The q parameter works on the documents' field names:

Example:

curl -k -G https://localhost:8443/ords/nimbus/route_docs/ \
  --data-urlencode 'q={"distanceKm":{"$gt":13000}}'

The response lists four routes: DXB to LAX and AKL, and back. In this test, equality filters on the CHAR(3) fields origin and destination, such as {"origin":"SIN"}, returned no documents, while numeric filters and {"_id":7} worked; test string filters on your own views.

Things to Know

• Give a duality view only the operations the API should allow; the annotations are the API's write rules.
• Clients must send the etag they read; ORDS and the database refuse stale updates with 412.
• Nested objects and arrays in a duality view come from joined tables, and ORDS returns them as they are defined.

Related Guides

• How to REST-Enable a Table with ORDS AutoREST
• How to Generate Duality Views with DBMS_JSON_DUALITY

Conclusion

ORDS publishes JSON relational duality views as document APIs with AutoREST: GET returns documents with etags, PUT updates them, and stale etags are refused with 412. Control what clients may change through the view's annotations.