PATCH & Oracle JSON Relational Duality Views REST APIs

Updated 15 May 2024 – this now reflects Oracle Database 23ai and ORDS 24.1 versions.

This is a follow-up to our discussion and demo of REST API support for JSON Relational Duality Views, so READ THIS FIRST.

ORDS 23.2 Adds Support for PATCH

PATCH is an HTTP method for doing partial updates to a resource, vs say a PUT that replaces (or adds) the ENTIRE resource.

If you’re using Database 23ai (23.4) – note you MUST be on at least ORDS version 24.1.

An Example

If we look at the OpenAPI doc for our races duality view, DRIVER_DV, we can see two new entries!

So I can PATCH a specific item, or I can use a query parameter filter to patch multiple records.

I want to update a RACE, but I don’t know the ID

Step 1: Find the Race

I’m looking for the race in Bahrain, so I will do a GET on race_dv/

GET http://localhost:8080/ords/janus/race_dv/?q={"name":{"$eq":"Bahrain Grand Prix"}}

And doing that I can see something like this –

So if I ONLY wanted to change the name of the race, I could either PATCH 201 or PATCH everything with the same query parameter filter.

Step 2: Patch the Races

Don’t like reading the docs? Enjoying running fast, with scissors? You may run into this.

Our Content-type for PATCH will be different than ‘application-json’ !

Our nicer, easier to use error messages are sharing we did something, wrong.

If we re-inspect the OpenAPI doc, we can see the PATCH is expecting a payload of type ‘application/merge-patch+json’

Step 2: Patch the Races, with the right Content Type

PATCH http://localhost:8080/ords/janus/race_dv/?q={"name":{"$eq":"Bahrain Grand Prix"}}
--header 'Content-Type: application/merge-patch+json' \
  --data '{"name": "Blue Air Bahrain Grand Prix"}'

One record found, one attribute of said record, updated!

If we pull up our races, we can see that race 201, and ONLY race 201 was updated.

Note the root record in a DV will have an _id attribute, this is new for 23ai over 23c.

What about PATCH for everything else?

Adding official PATCH support for your defined REST APIs and possible AUTOREST tables and views are on the drawing board, stay tuned!