7 Reasons to try out JSON Relational Duality Views, with samples

This story has migrated to my website — visit for the latest changes!

Oracle Database JSON Relational Duality Views provide a flexible, performant, and consistent solution for applications that require both relational and document-oriented data access, without the limitations of traditional ORMs or pure document databases:

1. Flexible data model: Duality Views store data in relational tables while allowing access as composable JSON documents, providing the benefits of both relational and document data models.
2. Simplified development: Duality Views eliminate the need for complex Object-Relational Mapping (ORM) layers like JPA. Developers can work directly with JSON documents without worrying about the underlying relational structure.
3. Improved performance: By efficiently storing data relationally and exposing it as JSON, Duality Views reduce the number of network round-trips other access models — On writes, each document is sent in its entirety to the database, where it is then split to the associated relational tables. During reads, the document is assembled in the database, and then sent directly to the user.
4. Data consistency and deduplication: Duality Views ensure data is consistent and eliminate duplication across multiple views, as all data is stored in a normalized relational format that accepts constraints.
5. Flexible access: Data can be accessed using SQL, REST APIs, document APIs (including MongoDB-compatible APIs), and various programming languages.
6. Multiple hierarchies: Developers can define different JSON views on the same underlying relational data, including role based access to the view definitions. Creating a similar model that includes RBAC without data duplication can be challenging with document databases.
7. Lock-free concurrency: Duality Views use a lock-free concurrency control mechanism, allowing for highly concurrent applications without the need for explicit locking.

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.

Let’s create a simple relational schema where students can 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, and another for students and their enrollments.

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 provide an appropriate level of write access to a view’s component tables.

-- 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 JSONB, which can be mapped using the language of your choice to objects or used as-is.

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 JSONB.

update students_dv v set data = // serialized student data
where v.data."_id" = 'student-id'

Looking for an in-depth example? Check out my article on using JSON Relational Duality Views with Java 21 and Spring Boot.

Learn more about JSON Relational Duality Views here.