Oracle Database 23ai JSON Relational Duality Views REST APIs

Updated 15 May 2024 – this post was originally written for 23c FREE and ORDS 23.1 – to follow along now, ensure you have Database 23ai (23.4) and at least ORDS 24.1.

Oracle Database 23ai has many marquee features, but JSON Relational Duality Views (from this point on, shortened to DVs), is probably the biggest game-changer development introduced in 23ai.

23ai Release Resources

What are Duality Views?

A very special view, allowing us to marry the best part of the relational data model (SQL!) and the best of working with JSON Objects and Documents (NoSQL!)

But I have a few friends that can give a MUCH better description than I –

Tables and JSON documents, living together…

Too long, didn’t watch? Ok, let me try to summarize.

A JSON-relational duality view exposes data stored in relational tables as JSON documents. The documents are materialized — generated on demand, not stored as such. Duality views give your data both a conceptual and an operational duality: it’s organized both relationally and hierarchically. You can base different duality views on data stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.

This means that applications can access (create, query, modify) the same data as a set of JSON documents or as a set of related tables and columns, and both approaches can be employed at the same time.

If you’re still not clear on the subject, may I suggest:

What we’ve built for REST API Support

If you have ORDS version 24.1 or higher, you can now REST Enable a 23ai DV, and you’ll get out-of-the-box, a complete set of REST APIs to work with your data.

OpenAPI documentation for an JSON Relational Duality View

Simply REST Enable the DV, and you’re good to go!

If you’re not familiar with our AutoREST feature or our REST Workshop in SQL Developer Web, I talk about both of those technologies here, a lot.

Finding a Demo Environment

You can follow along this exact demo from a tech brief we put together, or via a hosted Oracle Live Labs Tutorial. All of the code, data, and REST API calls are here.

One word about the tech brief- all of my code examples use cURL, and are run via Windows 11 CMD.

One word about the LiveLabs – you get a free, temporary hosted Oracle Cloud environment, so you can just run the lab, nothing to setup!

You could use our Docker and VirtualBox Appliances to run 23ai Free Developer Release locally on your machine.

The Code

I’m not going to share the code here – it’s in the tech brief and the Hands-On Lab. To summarize, you’ll be creating a schema (JANUS), several tables, and 3 DVs. The demo then uses the REST APIs to add teams, drivers, and races. Then we have drivers switch teams. We drop teams, we add race results, and we run lots of ‘queries.’

If you’re very impatient, here’s what a JSON-Relational Duality View’s DDL looks like.

Yes you can work with your Duality Views in our VS Code Extension!

Combining multiple elements across tables to build a single object, our DRIVER.

Batchload – loading multiple documents into a DV

I can POST up an array of JSON documents to the RACE_DV endpoint, and our REST API will add the records to the RACE_DV.

And the response?

The response is pretty basic, that we had 3 records, and 3 records were loaded.

HTTP/1.1 200 OK
#INFO Number of rows processed: 3
#INF0 Number of rows in error: 0
#INFO Last row processed in final committed batch: 3
SUCCESS: Processed without errors

GET the TEAMs

GET /ords/janus/team_dv/

We have four drivers between those two teams, let’s go look at the drivers. This time, I’ll just use by browser.

I asked for all the drivers, a GET on ords/janus/driver_dv/

Each driver’s “self” link is derived by the DRIVER_DV’s underlying root table’s PRIMARY KEY constraint. The Duality Views will often be comprised of multiple attributes across many tables, but there will generally be a single ‘root’ table. For the DRIVER_DV, that would be the DRIVER table.

Each object or document’s “etag” is derived by the record itself. These etags are extremely important, they allow you to assert the state of the object you are trying to update. If the object you are trying to update doesn’t have the etag you believe it should, the update will fail.

Having one PUT undo the work of the one prior to it is also known as a ‘lost update,’ the etag checking protects against that.

Read up on how the etags are computed here.

Updating Races with a PUT

I want to update the information for the Bahrain Grand Prix. All I need to do is a PUT on ords/janus/race_dv/201 – because that race’s ID value is ‘201.’

My PUT request is the actual JSON object or document, plus the _metadata.etag value. The link we see in responses are added by ORDS, but aren’t actually part of the object or document.

When I issue the PUT, if I include the etag for what I think is the current state of record 201, and I’m wrong, let’s say someone else has updated record 201 since I last looked at it, the PUT will fail with a HTTP 412: Precondition Failed

The etags don’t match, so the REST API protects us from accidently messing up the data.

Once I’ve acquired the correct etag from doing a GET on race 201, I can update my PUT request, and it’ll go through.

Let’s end on a filter

More often than not, we’re just looking to query our database. I mean, retrieve our documents or objects. So let’s do a GET with a query filter parameter.

I have several of these in the tech brief and hands-on lab, but here’s a fun one:

SQL: SELECT *
        FROM team
        WHERE name in ('Ferrari', 'Mercedes');

The REST API: GET on team_dv/?q={"name":{"$in":["Mercedes","Ferrari"]}}

GET ords/janus/team_dv/?q={“name”:{“$in”:[“Mercedes”,”Ferrari”]}}

There’s more, much more to explore

Watch the video. Read the docs. Try scanning the SQL and REST technical briefs. And then have a go at the software. Oracle-Base, that gem!, already some articles posted covering DVs.

His examples use employees and departments vs teams, drivers, and racers.

The AutoREST for JSON-Relational Duality Views are documented here.