andersswanson.dev

GraphQL syntax for JSON Relational Duality Views

Working with JSON Relational Duality Views, you can use the SQL or GraphQL DDL syntax. I like the GraphQL format for its simple, expressive syntax: in this article, we’ll explore using GraphQL to define for JSON Relational Duality Views through examples.

Want to follow along? Spin up an instance of Oracle Database Free – I suggest the latest available version.

JSON Relational Duality Views unify the capabilities of document and relational databases. for a primer on duality views, read my article 7 Reasons to try JSON Relational Duality Views.

A basic duality view

First, we create a relational table for a duality view. Let’s create a simple student table to start:

CREATE TABLE student (
    id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name  VARCHAR2(50) NOT NULL,
    email      VARCHAR2(100),
    major      VARCHAR2(20),
    credits    NUMBER(10),
    gpa        NUMBER(3,2)
        CHECK (gpa BETWEEN 0.00 AND 5.00)
);

After creating the table, we can define a basic duality view named student_dv using the GraphQL syntax:

-- if the view doesn't exist, replace it entirely.
create or replace force editionable json relational duality view student_dv -- creates a view named "student_dv"
 as student { -- use the "student" table for the root json object
    _id : id -- map student.id to _id
    firstName: first_name -- map student.first_name to firstName
    lastName : last_name -- map student.last_name to lastName
    email -- we choose not to rename any of the remaining columns
    major
    credits
    gpa
 };

Note the “_id” field in the duality view maps to the student.id column: this is always mandatory for a root ID in a duality view. Any table column name may be remapped to a JSON friendly name. Specifically, we’ll use camel case in our JSON view representation, excepting the mandatory “_id” column.

The prior view maps to a JSON object with the following schema. We’ll see this in action when we insert, select, and update database objects using the duality view.

{
  "_id": 1,
  "firstName": "Alice",
  "lastName": "Johnson",
  "email": "alice.johnson@example.edu",
  "major": "Computer Science",
  "credits": 45,
  "gpa": 3.85
}

@insert, @update, and @delete

By default, duality views are read-only. To make the view read-write, we apply the @insert, @update, and @delete annotations to tables or columns in a view.

Here’s how it’s done for the prior students view, applying @insert, @update, and @delete annotations after the table name:

create or replace force editionable json relational duality view student_dv
 as student @insert @update @delete {
    _id : id
    firstName: first_name
    lastName : last_name
    email
    major
    credits
    gpa
 };

With the annotations added, we insert a student document using the the view. See how “data” is always the root JSON object name for a duality view:

insert into student_dv (data) values ('{
  "firstName": "Alice",
  "lastName": "Johnson",
  "email": "alice.johnson@example.edu",
  "major": "Computer Science",
  "credits": 45,
  "gpa": 3.85
}');

Now, update our student document. Duality view updates are whole document updates:

update student_dv v set data = '{
  "_id": 1,
  "firstName": "Alice",
  "lastName": "Johnson",
  "email": "alice.johnson@example.edu",
  "major": "Computer Science",
  "credits": 50,
  "gpa": 3.89
}' where v.data."_id" = 1;

As we’re focusing on the GraphQL data definition syntax, I won’t dive into query semantics right now – Expect a dedicated follow up article that dives into querying JSON data. For now, read the JSON developer guide on querying.

Adding relationships

Single table views are fine, but duality views are relational. We’ll create a few more tables and relationships around the student table.

Next, we’ll add course and enrollment tables, so students can enroll in one or more courses. Each course may have one or more students, representing a many-to-many relationship:

Lcreate table course (
    id              number generated always as identity primary key,
    name            varchar2(50) not null,
    description     varchar2(250) not null,
    credits         number check (credits between 0 and 10)
);

-- a student may enroll in many courses
-- a course may have many students
create table enrollment (
    student_id number,
    course_id  number,
    -- the inclusion of foreign key constraints is always important:
    -- duality views automatically detect and apply foreign keys!
    constraint enrollment_pk primary key (student_id, course_id),
    constraint student_enrollment_fk foreign key (student_id)
    references student(id),
    constraint course_enrollment_fk foreign key (course_id)
    references course(id)
);

We’ll now update our duality view to include the new many-to-many relationship. Our goal is to include all of a student’s enrolled courses in a single, relational JSON document.

We use the @unnest keyword on the course table to un-nests each course object into the parent enrollment object. For example, @unnest is particularly useful for many-to-many relationships, as you avoid repeating join table keys:

create or replace force editionable json relational duality view student_dv
 as student @insert @update @delete {
    _id : id
    firstName: first_name
    lastName : last_name
    email
    major
    credits
    gpa
    -- Enrollments exist as a JSON array, as there may be multiple
    courses : enrollment @insert @delete [ {
      -- Courses are unnested into enrollments
      course @unnest @insert {
        _id : id
        name
        description
        credits
      }
    } ]
};

To insert into the new view, we can use a new JSON document that includes all of student, course and enrollment data. As we INSERT data, the database handles the creation of respective student, the enrollment, and the course records in one client round-trip:

-- The database inserts the student, enrollment, and course
-- into their respective tables efficiently and transparently.
insert into student_dv (data) values ('{
  "firstName": "Alice",
  "lastName": "Johnson",
  "email": "alice.johnson@example.edu",
  "major": "Computer Science",
  "credits": 45,
  "gpa": 3.85,
  "courses": [
    {
      "name": "Object Oriented Programming",
      "description": "The fine art of encapsulation and over-engineering",
      "credits": 4
    }
  ]
}');

One-to-many relationship

Next, add a one-to-many relationship, using a new assignment table in our student schema. Each course has one or more assignments for every student enrolled.

CREATE TABLE assignment (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id   NUMBER NOT NULL,
    title       VARCHAR2(100) NOT NULL,
    due_date    DATE,
    max_points  NUMBER CHECK (max_points > 0),
    CONSTRAINT assignment_course_fk FOREIGN KEY (course_id)
        REFERENCES course(id)
);

We update our duality view to include the assignment table, enabling students to view assignments for their enrolled courses.

create or replace force editionable json relational duality view student_dv
 as student @insert @update @delete {
    _id : id
    firstName: first_name
    lastName : last_name
    email
    major
    credits
    gpa
    courses : enrollment @insert @delete [ {
      course @unnest @insert {
        _id : id
        name
        description
        credits
        -- while assignments is a one-to-many, we don't
        -- have to define it as an array - it is implicit.
        assignments : assignment @insert {
          _id : id
          title
          due : due_date
          points : max_points
        }
      }
    } ]
};

Next, run the updated insert statement: creating the student, enrollment, course(s), and assignment(s) in a single request:

insert into student_dv (data) values ('{
  "firstName": "Alice",
  "lastName": "Johnson",
  "email": "alice.johnson@example.edu",
  "major": "Computer Science",
  "credits": 45,
  "gpa": 3.85,
  "courses": [
    {
      "name": "Object Oriented Programming",
      "description": "The fine art of encapsulation and over-engineering",
      "credits": 4,
      "assignments": [
        {
          "title": "Homework 1",
          "due": "2025-08-14",
          "points": 100
        }
      ]
    }
  ]
}');

Annotations

As we saw, duality views are modifiable with annotations like @insert, @update, @delete, and @unnest. Let’s cover each annotation in the GraphQL syntax and its usage.

@flex

A @flex column is a JSON Object column in a duality view that stores and merges dynamic fields not present in the schema. This object enables adding or modifying fields without altering the schema.

  1. On Read: The fields within the JSON object stored in a flex column are unpacked (unnested) into the resulting JSON document. As a result, any individual fields become top-level fields in the document, rather than being nested within an object.
  2. On Write: When inserting or updating a document, any new fields not represented by other columns are added automatically to the flex column’s JSON object.

Let’s try it out. We define a table with a JSON(OBJECT) column, include it in a duality view as a @flex field, and then insert a JSON document with dynamic fields:

CREATE TABLE product (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR2(100) NOT NULL,
    price       NUMBER(10,2),
    attributes  JSON(OBJECT)
);

create or replace force editionable json relational duality view product_dv
as product @insert @update {
    _id       : id,
    name,
    price,
    attributes @flex  -- any extra JSON fields go here
};

-- none of type, flavor, or origin are present in the schema.
insert into product_dv (data) values ('{
  "name": "Coffee Beans",
  "price": 10,
  "type": "Light Roast",
  "flavor": "citrus, chocolate",
  "origin": "Colombia"
}');

Within the database, the type, flavor, and origin fields are merged into the attributes JSON column.

The resulting attributes field will look like this, within the product table:

{"type":"Light Roast","flavor":"citrus, chocolate","origin":"Colombia"}

Because flexible JSON merging is automatic, conflicts may occur. There are some mitigations you can take to avoid conflicts, as seen here.

@nest, @unnest

The @nest and @unnest annotations allow you to reorganize the JSON schema of your relational data: we previously saw an example using @unnest to flatten a many-to-many relationship neatly. Here’s how to use @nest:

create or replace force editionable json relational duality view student_nested
 as student @insert @update @delete {
    _id : id
    -- the first_name, last_name, and email fields
    -- are nested into the personalInfo object
    personalInfo : student @nest {
        firstName: first_name
        lastName : last_name
        email
    }
    major
    credits
    gpa
};

An insert looks like this:

insert into student_nested (data) values ('{
  "personalInfo": {
    "firstName": "Alice",
    "lastName": "Johnson",
    "email": "alice.johnson@example.edu",
  },
  "major": "Computer Science",
  "credits": 45,
  "gpa": 3.85
}');

Both @nest and @unnest are great for manipulating tables and columns so your JSON documents fit your desired schema.

@hidden

The @hidden annotation actively hides specific fields from the JSON document. Unlike simply omitting a field, hidden fields can still be referenced by other sections of the view. Here’s an example that hides the gpa field:

create or replace force editionable json relational duality view student_no_gpa @insert @update @delete
 as student {
    _id : id
    firstName: first_name
    lastName : last_name
    email
    major
    credits
    gpa @hidden
};

A @hidden field is neither readable nor writable via the duality view.

@generated

The @generated annotation allows the inclusion of calculated fields in a duality view – generated fields are always read-only, and cannot include any other annotation.

Next, we create a view that uses a few generated fields. For example purposes, we’ll use both SQL and JSON path expressions for generated fields. You can also use hidden fields in generated fields!

create or replace force editionable json relational duality view gpa_summary
 as student @insert @update @delete {
    _id : id
    -- create a generated field using SQL
    maxGpa @generated (sql : "SELECT max(s.gpa) from student s")
    -- create a generated field using JSON Path expressions
    avgGradePerCourse @generated (path : "$.credits / $.gpa")

    -- hidden fields can be used in generated fields!
    credits @hidden
    gpa @hidden
};

The @link annotation defines foreign key relationships and primarily disambiguates multiple foreign keys.

Because duality view relationships can automatically detected through foreign key constraints, you often don’t need @link. However, when foreign keys are ambiguous, you must use @link.

Let’s see an example: Here, each team has multiple foreign keys to a person. See how @link disambiguates foriegn keys when we include both a manager and assistant in the team duality view:

CREATE TABLE person (
  person_id NUMBER PRIMARY KEY,
  name      VARCHAR2(100)
);

CREATE TABLE team (
  team_id      NUMBER PRIMARY KEY,
  name         VARCHAR2(100),
  -- A team links to multiple person records
  manager_id   NUMBER,
  assistant_id NUMBER,
  FOREIGN KEY (manager_id) REFERENCES person(person_id),
  FOREIGN KEY (assistant_id) REFERENCES person(person_id)
);

create or replace force editionable json relational duality view team_dv
as team {
  _id       : team_id,
  name,
  -- manager is linked to manager_id
  manager   : person @link (from : ["MANAGER_ID"]) {
    _id : person_id,
    name
  },
  -- assistant is linked to assistant_id
  assistant : person @link (from : ["ASSISTANT_ID"]) {
    _id : person_id,
    name
  }
};

If you have specific foreign key constraints, you do not need to use @link – in fact, all prior examples didn’t require @link.

@insert, @update, @delete & @noinsert, @noupdate, @nodelete

The write access annotations may be applied on the table and/or column level, and can be combined with @noinsert, @noupdate, and @nodelete annotations to limit access. Remember that duality views are by default read-only, and ID fields are always read-only.

Here’s a couple examples. Here, we restrict updates to the signupDate field, as it should not be changed after creation:

CREATE TABLE user_account (
    user_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username    VARCHAR2(50) NOT NULL UNIQUE,
    -- created on insert, and never changed
    signup_date DATE DEFAULT SYSDATE NOT NULL,
    status      VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL
        CHECK (status IN ('ACTIVE', 'SUSPENDED', 'CLOSED'))
);

create or replace force editionable json relational duality view user_dv
 as user_account @insert @update @delete {
    _id : user_id
    username
    -- user_dv is updateable, but not the signupDate field
    signupDate : signup_date @noupdate
    status
};

If we try to update the signupDate field, an ORA-40940: Cannot update field is raised due to the @noupdate annotation:

insert into user_dv (data) values('{
  "username": "Anders Swanson",
  "status": "ACTIVE"
}');

update user_dv v set data = '{
  "_id": 1,
  "username": "Anders Swanson",
  "signupDate": "2025-08-01",
  "status": "ACTIVE"
}' where v.data."_id" = 1;

Next, the following view is read only except for the quantity column:

create table snacks (
    id number generated always as identity primary key,
    name varchar2(50),
    quantity number
);
create or replace force editionable json relational duality view snacks_dv
 as snacks { -- view is read only at the top level
    _id : id
    name
    quantity @update
};

Only the quantity field is update-able:

insert into snacks (name, quantity) 
values ('Chocolate Chip Cookies', 12);
update snacks_dv v set data = '{
    "_id": 1,
    "name": "Chocolate Chip Cookies",
    "quantity": 11
}' where v.data."_id" = 1;

If we try any other write operation, it will fail with an ORA-40940 error or similar.

@check, @nocheck

The @check and @nocheck annotations control which tables and columns of a duality view contribute to optimistic concurrency control:

  1. When a JSON document is retrieved via a duality view, it includes an internal _metadata object that contains an etag value: a hash representing the document’s current state.
  2. On update, the database compares the submitted etag against the document’s current state. If they match (i.e., the document hasn’t changed in the meantime), the update succeeds; if not, the update fails.

By default, columns and tables in a duality view are checked. We can modify this behavior by using and combining the @check and @nocheck annotations. Let’s see an example:

create or replace force editionable json relational duality view snacks_nocheck
 as snacks @insert @update @delete {
    _id : id
    name
    quantity @nocheck
};

With this view, the name field will be checked against the database etag, but the quantity field will not.

References

To learn more about duality views and JSON in the database, check out my related articles or consult the Oracle Documentation

Response

  1. Reading List – August 2025 #2 – Gerald on IT

    […] GraphQL syntax for JSON Relational Duality Views – Anders Swanson dives into GraphQL for JSON Relational Duality […]

    Reply

Leave a Reply

Discover more from andersswanson.dev

Subscribe now to keep reading and get access to the full archive.

Continue reading