Oracle Database 23c JSON Relational Duality Views REST APIs

Oracle Database 23c Free Developer Release 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 23c.

Oracle Database 23c Free – Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

23c Free Developer Release Resources

• Release Press
• Announcement Blog
• Documentation – New Features Guide
• Get Started – Links, Downloads, Connection/Driver examples

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 –

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

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

• Duality View benefits blog
• 23c Duality View Docs

What we’ve built for REST API Support

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

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.

• Tech Brief/Tutorials
• LiveLabs Tutorial

One word about the tech brief- all of my code examples use cURL, and are run via Windows 10 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 23c 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.

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 TEAM_DV endpoint, and our REST API will add the records to the TEAM_DV. Meanwhile, since are TEAMs are composed of DRIVERs – the DRIVER TABLE and the DRIVER_DV will also show our new DRIVERs added via the two teams that were just processed.

The REST Workshop in SQL Developer Web includes the OpenAPI doc/client.

And the response?

I like that the tooling shows what the equivalent cURL would be. And result, 2 new teams have been added. But in reality that’s 4 new rows across two different tables from the single POST.

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.

Two notes to end on:

• Partial doc updates with PATCH are not yet supported.
• The AutoREST for JSON-Relational Duality Views are documented here.