Exploring JSON-Relational Duality Views in Oracle Database 23c Free — Developer Release
JSON-relational duality views combine the advantages of using JSON documents with the advantages of the relational model, while avoiding the limitations of each. It exposes data stored in relational tables as JSON documents, which are generated on demand, not stored as such.
Let’s take an in-depth look at how we can use this new and exciting feature with REST APIs via ORDS.
To access JSON-Relational duality views through REST APIs, Oracle REST Data Services (ORDS) version 23.1 needs to be installed and configured for your 23c Database.
If you’re looking for an installation guide, make sure to check out the link below.
What are JSON-Relational Duality Views?
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.
How are JSON-Relational Duality Views used?
Document-centric applications can use document APIs, such as Oracle Database API for MongoDB, and Oracle REST Data Services (ORDS), or they can use SQL/JSON document functions.
You can manipulate documents realized by duality views in the ways you’re used to, using your usual drivers, frameworks, tools, and development methods. In particular, applications can use any programming languages — JSON documents are the lingua franca.
Other applications, such as database analytics, reporting, and machine learning, can make use of the same data directly, relationally (as a set of table rows and columns), using languages such as SQL, PL/SQL, C, and JavaScript. You need not adapt an existing database feature or code that makes use of table data to instead use JSON documents.
A duality view can be read-only or completely or partially updatable, depending on how you define it. You can define a duality view, and its updatability, declaratively (what/where, not how), using SQL or a subset of the GraphQL language.
How do modifications work?
When you modify a duality view — to insert, delete, or update JSON documents, the relevant relational (table) data underlying the view is automatically updated accordingly.
Conversely, if you modify data in tables that underlie one or more duality views then those changes are automatically and immediately reflected in the documents supported by those views.
Car Racing Example
Let’s start exploring JSON-relational duality views using the car racing example below.
For this exercise, I will be using Visual Studio Code with the Oracle Developer Tools and Thunder Client extensions installed, to access Oracle Database and the ORDS APIs, respectively. Please feel free to use the IDEs or applications you’re most comfortable with.
Data for Formula 1 car races is used in this example to present the new features. We suppose a document-centric application that uses three kinds of JSON documents: driver, race, and team. Each of these kinds shares some data with another kind. For example:
- A driver document includes, in its information about a driver, identification of the driver’s team and information about the racesthe driver has participated in.
- A race document includes, in its information about a particular race, information about the podium standings (first-, second-, and third-place winners), and the results for each driver in the race. Both of these include driver and team names. The racing data is for a single season of racing.
- A team document includes, in its information about a team, information about the drivers on the team.
Operations the application might perform on this data include the following:
- Adding or removing a driver, race, or team to/from the database
- Updating the information for a driver, race, or team
- Adding a driver to a team, removing a driver from a team, or moving a driver from one team to another
- Adding race results to the driver and race information
Setting up the schema and enabling ORDS
- In Visual Studio Code — Oracle Developer Tools, connect to your Oracle Database 23c — Developer Release as sysdba:
The tnsnames.ora file that we’re using in Visual Studio Code looks like this, where you should replace the [IP] placeholder with the IP address of your Compute instance:
# tnsnames.ora Network Configuration File
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))- Open a new SQL file and create a database schema that will host JSON-Relational duality views and their tables. In this guide, I will be using RDVTEST as the schema name.
create user RDVTEST identified by [password];
grant create session to RDVTEST;
grant RESOURCE to RDVTEST;
grant unlimited tablespace to RDVTEST;
- Once this is done, we can log in as the newly created user. We’ll update the connection in Visual Studio Code with the new user and their password, close all the open files, and reconnect.
- In a new SQL file, we will need to enable ORDS for this schema.
exec ords.enable_schema;
commit;
With that, ORDS will serve APIs at the following base URI:
http://localhost:8080/ords/rdvtest
Since in this exercise we’re remotely connecting to a database running on a Compute instance in OCI (like shown in the installation guide linked above), we’ll be using the IP address of the instance instead of localhost in the screenshots that you’ll see below.
What is AutoREST?
Before continuing, let’s talk a bit first about AutoREST.
Publishing REST APIs for your database objects can be as easy as simply enabling them for access. Our REST API technology includes a feature known as AutoREST, where one or more objects are enabled, and REST API endpoints are automatically published.
For example, a table can be enabled for GET, PUT, POST, DELETE operations to get one or more rows, insert or update rows, delete rows, or even batch load multiple rows in a single request.
This feature has been enhanced for 23c to include similar REST access for JSON-Relational duality views.
For the sake of simplicity, the REST APIs in this exercise are unprotected.
Oracle Database REST APIs offer performance and secure access for application developers, and it is highly recommended you protect your endpoints with the proper web privileges and roles.
Creating JSON-Relational Duality Views
- [OPTIONAL] To start with a clean slate, if you’ve run this exercise before, we can first drop all the tables and views if they exist in the schema already:
drop view if exists team_dv;
drop view if exists race_dv;
drop view if exists driver_dv;
drop table if exists driver_race_map;
drop table if exists race;
drop table if exists driver;
drop table if exists team;- Create the TEAM, DRIVER, RACE, and DRIVER_RACE_MAPS tables
CREATE TABLE IF NOT EXISTS TEAM (
TEAM_ID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
NAME VARCHAR2(255) NOT NULL UNIQUE,
POINTS INTEGER NOT NULL,
CONSTRAINT TEAM_PK PRIMARY KEY(TEAM_ID)
);
CREATE TABLE IF NOT EXISTS DRIVER (
DRIVER_ID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
NAME VARCHAR2(255) NOT NULL UNIQUE,
POINTS INTEGER NOT NULL,
TEAM_ID INTEGER,
CONSTRAINT DRIVER_PK PRIMARY KEY(DRIVER_ID),
CONSTRAINT DRIVER_FK FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID)
);
CREATE TABLE IF NOT EXISTS RACE (
RACE_ID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
NAME VARCHAR2(255) NOT NULL UNIQUE,
LAPS INTEGER NOT NULL,
RACE_DATE DATE,
PODIUM JSON,
CONSTRAINT RACE_PK PRIMARY KEY(RACE_ID)
);
CREATE TABLE IF NOT EXISTS DRIVER_RACE_MAP (
DRIVER_RACE_MAP_ID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
RACE_ID INTEGER NOT NULL,
DRIVER_ID INTEGER NOT NULL,
POSITION INTEGER,
CONSTRAINT DRIVER_RACE_MAP_PK PRIMARY KEY(DRIVER_RACE_MAP_ID),
CONSTRAINT DRIVER_RACE_MAP_FK1 FOREIGN KEY(RACE_ID) REFERENCES RACE(RACE_ID),
CONSTRAINT DRIVER_RACE_MAP_FK2 FOREIGN KEY(DRIVER_ID) REFERENCES DRIVER(DRIVER_ID)
);
- Create a trigger on the DRIVER_RACE_MAP table to populate the POINTS column in the TEAM and DRIVER tables based on race results
CREATE OR REPLACE TRIGGER DRIVER_RACE_MAP_TRIGGER BEFORE
INSERT ON DRIVER_RACE_MAP FOR EACH ROW
DECLARE
V_POINTS INTEGER;
V_TEAM_ID INTEGER;
BEGIN
SELECT
TEAM_ID INTO V_TEAM_ID
FROM
DRIVER
WHERE
DRIVER_ID = :NEW.DRIVER_ID;
IF :NEW.POSITION = 1 THEN
V_POINTS := 25;
ELSIF :NEW.POSITION = 2 THEN
V_POINTS := 18;
ELSIF :NEW.POSITION = 3 THEN
V_POINTS := 15;
ELSIF :NEW.POSITION = 4 THEN
V_POINTS := 12;
ELSIF :NEW.POSITION = 5 THEN
V_POINTS := 10;
ELSIF :NEW.POSITION = 6 THEN
V_POINTS := 8;
ELSIF :NEW.POSITION = 7 THEN
V_POINTS := 6;
ELSIF :NEW.POSITION = 8 THEN
V_POINTS := 4;
ELSIF :NEW.POSITION = 9 THEN
V_POINTS := 2;
ELSIF :NEW.POSITION = 10 THEN
V_POINTS := 1;
ELSE
V_POINTS := 0;
END IF;
UPDATE DRIVER
SET
POINTS = POINTS + V_POINTS
WHERE
DRIVER_ID = :NEW.DRIVER_ID;
UPDATE TEAM
SET
POINTS = POINTS + V_POINTS
WHERE
TEAM_ID = V_TEAM_ID;
END;
/
- Create the RACE_DV, DRIVER_DV, and TEAM_DV JSON-Relational duality views
-- Create race view, RACE_DV
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW RACE_DV AS
SELECT
JSON {'raceId' IS R.RACE_ID,
'name' IS R.NAME,
'laps' IS R.LAPS WITH NOUPDATE,
'date' IS R.RACE_DATE,
'podium' IS R.PODIUM WITH NOCHECK,
'result' IS [
SELECT
JSON {'driverRaceMapId' IS DRM.DRIVER_RACE_MAP_ID,
'position' IS DRM.POSITION,
UNNEST (
SELECT
JSON {'driverId' IS D.DRIVER_ID,
'name' IS D.NAME}
FROM
DRIVER D WITH NOINSERT UPDATE NODELETE
WHERE
D.DRIVER_ID = DRM.DRIVER_ID)}
FROM
DRIVER_RACE_MAP DRM WITH INSERT UPDATE DELETE
WHERE
DRM.RACE_ID = R.RACE_ID ]}
FROM
RACE R WITH INSERT UPDATE DELETE;
-- Create driver view, DRIVER_DV
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW DRIVER_DV AS
SELECT
JSON {'driverId' IS D.DRIVER_ID,
'name' IS D.NAME,
'points' IS D.POINTS,
UNNEST (
SELECT
JSON {'teamId' IS T.TEAM_ID,
'team' IS T.NAME WITH NOCHECK}
FROM
TEAM T WITH NOINSERT NOUPDATE NODELETE
WHERE
T.TEAM_ID = D.TEAM_ID),
'race' IS [
SELECT
JSON {'driverRaceMapId' IS DRM.DRIVER_RACE_MAP_ID,
UNNEST (
SELECT
JSON {'raceId' IS R.RACE_ID,
'name' IS R.NAME}
FROM
RACE R WITH NOINSERT NOUPDATE NODELETE
WHERE
R.RACE_ID = DRM.RACE_ID),
'finalPosition' IS DRM.POSITION}
FROM
DRIVER_RACE_MAP DRM WITH INSERT UPDATE NODELETE
WHERE
DRM.DRIVER_ID = D.DRIVER_ID ]}
FROM
DRIVER D WITH INSERT UPDATE DELETE;
-- Create team view, TEAM_DV
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEAM_DV AS
SELECT
JSON {'teamId' IS T.TEAM_ID,
'name' IS T.NAME,
'points' IS T.POINTS,
'driver' IS [
SELECT
JSON {'driverId' IS D.DRIVER_ID,
'name' IS D.NAME,
'points' IS D.POINTS WITH NOCHECK}
FROM
DRIVER D WITH INSERT UPDATE
WHERE
D.TEAM_ID = T.TEAM_ID ]}
FROM
TEAM T WITH INSERT UPDATE DELETE;
- Enable the Duality Views for REST APIs. Make sure to replace the schema name in each PL/SQL statement below if necessary.
Note the P_OBJECT_ALIAS parameter defines the URI, making the duality views addressable in the REST APIs. These aliases are customizable and can be used to hide database implementation details from your REST API consumers.
BEGIN
ORDS.ENABLE_OBJECT(
P_ENABLED => TRUE,
P_SCHEMA => 'RDVTEST',
P_OBJECT => 'DRIVER_DV',
P_OBJECT_TYPE => 'VIEW',
P_OBJECT_ALIAS => 'driver_dv',
P_AUTO_REST_AUTH => FALSE
);
COMMIT;
END;
/
BEGIN
ORDS.ENABLE_OBJECT(
P_ENABLED => TRUE,
P_SCHEMA => 'RDVTEST',
P_OBJECT => 'RACE_DV',
P_OBJECT_TYPE => 'VIEW',
P_OBJECT_ALIAS => 'race_dv',
P_AUTO_REST_AUTH => FALSE
);
COMMIT;
END;
/
BEGIN
ORDS.ENABLE_OBJECT(
P_ENABLED => TRUE,
P_SCHEMA => 'RDVTEST',
P_OBJECT => 'TEAM_DV',
P_OBJECT_TYPE => 'VIEW',
P_OBJECT_ALIAS => 'team_dv',
P_AUTO_REST_AUTH => FALSE
);
COMMIT;
END;
/
Testing the Duality Views
OpenAPI Documentation
The OpenAPI information for our APIs is available at the following URIs:
http://localhost:8080/ords/rdvtest/open-api-catalog/team_dv/
http://localhost:8080/ords/rdvtest/open-api-catalog/driver_dv/
http://localhost:8080/ords/rdvtest/open-api-catalog/race_dv/You can copy and paste the JSON returned by those URIs in the Swagger Editor, to get the auto-generated definitions of each endpoint.
List all documents in a duality view
To list all documents in a duality view, issue a GET request with the corresponding collection name.
For example, to list all documents in the DRIVER_DV duality view, run:
curl -X GET http://localhost:8080/ords/rdvtest/driver_dv/
Populate duality views
Single document insert
To insert a single document use POST, with the document content provided as the body.
Note that while the write operations in this tutorial are performed on duality views, the actual data is transparently written to the underlying tables (as the duality views themselves do not hold any data).
POST http://localhost:8080/ords/rdvtest/team_dv/{
"teamId": 2,
"name": "Mercedes",
"points": 0,
"driver": [
{
"driverId": 105,
"name": "George Russell",
"points": 0
},
{
"driverId": 106,
"name": "Lewis Hamilton",
"points": 0
}
]
}
Oracle Database generates an eTag for each document inserted into the duality view. This information is returned under the _metadata field in the response body. These eTags are critical for upcoming examples involving updates using PUTs.
In addition, the response includes a Content-Location header redirecting the client to the new resource, in this case the Mercedes team. The self link shows where to address the new record. The record locator is derived from the TEAM primary key column, in this case, teamId.
Bulk insert
You can also insert multiple records into a duality view in one request, using POST to the REST duality view, with the /batchload URI and an accompanying array of JSON objects in the request body.
POST http://localhost:8080/ords/dvtest/team_dv/batchload[
{
"teamId": 301,
"name": "Red Bull",
"points": 0,
"driver": [
{
"driverId": 101,
"name": "Max Verstappen",
"points": 0
},
{
"driverId": 102,
"name": "Sergio Perez",
"points": 0
}
]
},
{
"teamId": 302,
"name": "Ferrari",
"points": 0,
"driver": [
{
"driverId": 103,
"name": "Charles Leclerc",
"points": 0
},
{
"driverId": 104,
"name": "Carlos Sainz Jr",
"points": 0
}
]
}
]
The AutoREST Duality View API includes a POST /batchload endpoint for batch loading multiple JSON documents as rows in the view.
The response body is a report showing the number of records processed, rows that failed with an error, and rows successfully committed to the underlying duality views.
Let’s do the same for the race information:
POST http://localhost:8080/ords/dvtest/race_dv/batchload[
{
"raceId": 201,
"name": "Bahrain Grand Prix",
"laps": 57,
"date": "2022-03-20T00:00:00",
"podium": {}
},
{
"raceId": 202,
"name": "Saudi Arabian Grand Prix",
"laps": 50,
"date": "2022-03-27T00:00:00",
"podium": {}
},
{
"raceId": 203,
"name": "Australian Grand Prix",
"laps": 58,
"date": "2022-04-09T00:00:00",
"podium": {}
}
]
Query Parameters
Query Parameters are pattern-like queries for JSON data, also expressed in JSON.
Records satisfying specific conditions can be retrieved by supplying query parameters. Let’s say we want to pull up a particular race, the Bahrain Grand Prix.
We will supply them in the q parameter of our REST request.
http://localhost:8080/ords/rdvtest/race_dv/?q=[filter]Equals
This operation uses query parameters with the following content:
{"name":{"$eq":"Bahrain Grand Prix"}}This query parameter will find all records having that name. To retrieve records matching this filter run:
curl -X GET \
'http://localhost:8080/ords/rdvtest/race_dv/?q=%7B%22name%22%3A%7B%22%24eq%22%3A%22Bahrain%20Grand%20Prix%22%7D%7D'
Note also that the individual race can be retrieved by the raceId attribute. Since the underlying RACE table has a primary key defined on RACE_ID, the corresponding AutoREST enabled Duality View’s raceId attribute can be used as a key for working with specific races.
Replace a record identified by the Duality View ID (table Primary Key)
To replace a target document, use PUT with the ID as the trailing step. Let’s update the Bahrain Grand Prix and add the podium information.
PUT http://localhost:8080/ords/rdvtest/race_dv/201{
"_metadata": {
"etag": "2E8DC09543DD25DC7D588FB9734D962B"
},
"raceId": 201,
"name": "Bahrain Grand Prix",
"laps": 57,
"date": "2022-03-20T00:00:00",
"podium": {
"winner": {
"name": "Charles Leclerc",
"time": "01:37:33.584"
},
"firstRunnerUp": {
"name": "Carlos Sainz Jr",
"time": "01:37:39.182"
},
"secondRunnerUp": {
"name": "Lewis Hamilton",
"time": "01:37:43.259"
}
},
"result": [
{
"driverRaceMapId": 3,
"position": 1,
"driverId": 103,
"name": "Charles Leclerc"
},
{
"driverRaceMapId": 4,
"position": 2,
"driverId": 104,
"name": "Carlos Sainz Jr"
},
{
"driverRaceMapId": 9,
"position": 3,
"driverId": 106,
"name": "Lewis Hamilton"
},
{
"driverRaceMapId": 10,
"position": 4,
"driverId": 105,
"name": "George Russell"
}
]
}Note that the eTag value supplied in the content is used for out-of-the-box optimistic locking, to prevent the “lost update” problem that can occur with concurrent operations.
During the replace by ID operation, the database checks that the eTag provided in the replacement document matches the latest eTag of the target duality view document. If the eTags do not match, which can occur if another concurrent operation updated the same document, an error is thrown.
In case of such an error, you can reread the updated value (including the updated eTag), and retry the replace operation again, adjusting it (if desired) based on the updated value.
Updating the race again requires a valid eTag. This can be retrieved with a HEAD request.
curl -i -X HEAD http://localhost:8080/ords/rdvtest/race_dv/201Greater Than
Let’s use the greater than query parameter to retrieve information.
{"points":{"$gt":40}}This query parameter will match all documents that have the points field greater than 40. To fetch documents matching this query filter parameter run:
curl -X GET \
'http://localhost:8080/ords/rdvtest/team_dv?q=%7B%22points%22%3A%7B%22%24gt%22%3A40%7D%7D'
Re-parenting of sub-objects between two documents
Switching Charles Leclerc’s and George Russell’s teams can be done by updating the driver arrays in Mercedes and Ferrari documents of TEAM_DV duality view.
In
First we’ll have to find out the IDs of the Mercedes and Ferrari teams using the following query parameter filter:
{"name":{"$in":["Mercedes","Ferrari"]}}curl -X GET \
'http://localhost:8080/ords/rdvtest/team_dv?q=%7B%22name%22%3A%7B%22%24in%22%3A%5B%22Mercedes%22%2C%22Ferrari%22%5D%7D%7D'
The ID for the Mercedes team is 2, and the ID for the Ferrari team is 302.
Now we can update both of the teams, using the following JSON documents:
PUT http://localhost:8080/ords/rdvtest/team_dv/2{
"_metadata": {
"etag": "855840B905C8CAFA99FB9CBF813992E5"
},
"teamId": 2,
"name": "Mercedes",
"points": 40,
"driver": [
{
"driverId": 106,
"name": "Lewis Hamilton",
"points": 15
},
{
"driverId": 103,
"name": "Charles Leclerc",
"points": 25
}
]
}
PUT http://localhost:8080/ords/dvtest/team_dv/302{
"_metadata": {
"etag": "DA69DD103E8BAE95A0C09811B7EC9628"
},
"teamId": 302,
"name": "Ferrari",
"points": 30,
"driver": [
{
"driverId": 105,
"name": "George Russell",
"points": 12
},
{
"driverId": 104,
"name": "Carlos Sainz Jr",
"points": 18
}
]
}
After these operations, Leclerc appears under the Mercedes team and no longer under Ferrari, and Russell appears under the Ferrari team and no longer under Mercedes, respectively.
Or and Like
The DRIVER_DV duality view has been updated as well. This can be verified by using the following query parameter filter:
{"$or" : [{"name" : {"$like" : "George%"}}, {"name" : {"$like" : "Charles%"}}]}curl -X GET \
'http://localhost:8080/ords/rdvtest/driver_dv?q=%7B%22%24or%22%20%3A%20%5B%7B%22name%22%20%3A%20%7B%22%24like%22%20%3A%20%22George%25%22%7D%7D%2C%20%7B%22name%22%20%3A%20%7B%22%24like%22%20%3A%20%22Charles%25%22%7D%7D%5D%7D'
Delete documents
Target document identified by document key
You can delete a document with a given document key, in other words, its ID. For example, to delete the Bahrain Grand Prix document, we can run the following query:
curl -X DELETE \
'http://localhost:8080/ords/dvtest/race_dv/201'
Target document(s) identified by a query parameter filter
We can also delete records matching a query parameter filter. For example:
{"raceId":{"$eq":202}}curl -X DELETE \
'http://localhost:8080/ords/rdvtest/race_dv/?q=%7B%22raceId%22%3A%7B%22%24eq%22%3A202%7D%7D'
Conclusion
We looked together at some of the basics of the new JSON-Relational Duality View functionality, and how the REST APIs work.
The following operations were covered:
- Enabling a JSON-Relational duality view for REST API access
- Fetching a record by its primary key
- Fetching documents using query parameter filters
- Fetching all records in a duality view
- Replacing a record in a duality view, with optional eTag checking
- Deleting a document identified by its document key
- Deleting documents identified by a query filter parameter
Documentation
For more information on JSON-Relational duality views, make sure to check out the JSON-Relational Duality Developer’s Guide.
For more information on the AutoREST APIs support for Oracle Database objects used for this example, see the ORDS Developer’s Guide.
For an overview of query parameter filters using JSON syntax see Filtering in Queries.
Your feedback is important to us!
Connect with us if you have any questions or feedback on Oracle Database 23c Free — Developer Release:
Happy coding!
