Oracle Database JSON Relational Duality Views provide a flexible, performant, and consistent solution for applications that require document data access, relational data access, or both simultaneously. Duality Views use normalized, relational data structures, differing from document databases.
Let’s take a look at the key features of JSON Relational Duality Views:
- Flexible data modeling: Think SQL meets document databases. Relational tables store data, but you access it via composable JSON documents, granting you the flexibility of either data model.
- Flexible data access: Data is accessible via SQL, REST APIs (Oracle REST Data Services), document APIs (including MongoDB-compatible APIs), and database drivers, offering a wide variety of options for clients.
- Drop the ORM or Document DB: Work directly with JSON documents, serializing or de-serializing your data to Oracle Database’s binary JSON format (OSON).
- One dound-trip, no joins: Query nested, relational JSON documents with a single round-trip. The database server efficiently handles the hard part of joining and shredding documents to their backing tables.
- Data consistency and de-duplication: Data is consistent, without duplication across multiple views. Data is stored in a normalized relational format with constraints.
- Multiple views, one schema: Define role-based, read-write views for different personas, allowing you to expose the same schema with different permissions and column-level visibility controls. Creating a similar RBAC-enabled without data duplication can be challenging with document databases.
- Lock-free concurrency: Duality Views use a lock-free, optimistic concurrency control mechanism that allows concurrent applications to read and write data without the need for explicit locking.
Bonus: you can auto-generate REST APIs for your duality views using Oracle REST Data Services, spinning up a full CRUD app in minutes with 0 lines of backend code.
OK, I’m sold — let’s try defining a couple duality views using SQL
Hold on — before we creating any duality views, we need to define a backing relational schema. If you already have a relational schema, you can layer duality views on top of it without interruption.
To start, let’s create a simple relational schema. In our schema, students enroll in courses, and each course is associated with a lecture hall. We’ll use this schema as the relational backing for multiple duality views.
create table students (
id varchar2(36) default sys_guid() primary key,
first_name varchar2(50) not null,
last_name varchar2(50) not null,
email varchar2(100),
major varchar2(50) not null,
credits number(10),
gpa binary_double
);
create table lecture_halls (
id varchar2(36) default sys_guid() primary key,
name varchar2(50) not null
);
-- Each course is associated with a lecture hall
create table courses (
id varchar2(36) default sys_guid() primary key,
lecture_hall_id varchar2(36) not null,
name varchar2(50) not null,
description varchar2(250) not null,
credits number check (credits between 0 and 10),
constraint lecture_hall_fk foreign key (lecture_hall_id)
references lecture_halls(id)
);
-- A student may be enrolled in one or more courses
create table enrollments (
id varchar2(36) default sys_guid() primary key,
student_id varchar2(36) not null,
course_id varchar2(36) not null,
constraint student_fk foreign key (student_id)
references students(id),
constraint course_fk foreign key (course_id)
references courses(id)
);
We’ll now create two read-write duality views over the relational schema, one for courses (courses_dv), and another for students and their enrollments (students_dv).
The courses_dv view provides access to a course document, with that course’s associated lecture hall. Note the use of @insert, @update, and @delete annotations to that modify the view’s level of access to relational table data.
-- Course/lecture hall view to access courses outside of a student context.
create or replace json relational duality view
courses_dv as
courses @insert @update @delete {
_id : id,
name,
description,
credits,
lecture_hall: lecture_halls @insert @update {
_id: id,
name
}
}The students_dv view includes a student’s personal information, their course enrollments, and associated course lecture halls. This view provides a student-centric focus, only including course information for that student’s active enrollments.
-- View containing a student, their enrollments, and related course data
-- for each enrollment.
create or replace json relational duality view
students_dv as
students @insert @update @delete {
_id : id,
first_name,
last_name,
email,
major,
gpa,
credits,
-- Due to implicit nesting, enrollments will only contain data
-- related to the containing student.
enrollments : enrollments @insert @update @delete
{
_id : id,
course : courses @insert @update {
_id : id,
name,
description,
credits,
lecture_hall: lecture_halls @insert @update {
_id: id,
name
}
}
}
};Retrieving, inserting, and updating views
The following query retrieves students with the first name ‘alice’ and the last name ‘johnson’ from the students_dv view. Queries against duality views return serialized OSON, which can be mapped using the language of your choice to objects or used as-is.
If you’re not familiar with OSON, it’s Oracle Database’s efficient binary JSON format. You can read more about OSON here.
select * from students_dv v
where v.data.first_name = 'alice'
and v.data.last_name = 'johnson'The following query updates a student document, using serialized OSON.
update students_dv v set data = // serialized student data
where v.data."_id" = 'student-id'References
Questions? Leave a comment or connect on LinkedIn.
Leave a Reply