JSON-relational duality view in Oracle AI Database 26ai is an advanced feature that enables the same underlying relational data to be accessed and manipulated either as hierarchical JSON documents or as traditional SQL tables without any duplication.

By bridging the gap between relational and document models, duality views provide application developers with the flexibility of JSON and the consistency, efficiency, and normalization benefits of the relational model. With a duality view, data is always stored just once in relational tables, but you can surface and interact with it in whichever format your application needs. The database engine automatically maps tables and columns to nested JSON structures. Any changes, whether made through the JSON view or via SQL, are instantly synchronized, ensuring a single, up-to-date source of truth regardless of how the data is accessed.

Oracle AI Database offers a modern way to define JSON-relational duality views using a concise, GraphQL-based syntax. This approach allows developers to express how relational data should be projected as hierarchical JSON documents directly inside the database, leveraging familiar GraphQL concepts like nested fields, aliases, and directives. The database automatically infers joins and relationships from foreign keys, so there is no need for complex subquery or join declarations. Additionally, GraphQL directives such as @insert, @update, @delete, and @nocheck give fine-grained control over how data can be manipulated and validated at each level of the JSON hierarchy. By adopting this syntax, you get the best of both relational integrity and JSON flexibility, enabling seamless access and updates through SQL and GraphQL APIs without duplicating data or logic. This ability streamlines the integration of document style views with your GraphQL based applications, letting you define and expose relational data as hierarchical JSON documents without leaving the GraphQL environment.

CREATE TABLE team
  (team_id    INTEGER PRIMARY KEY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL);

CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
  team @insert @update @delete
    {_id    : team_id,
     name   : name,
     points : points,
     driver : driver @insert @update
       [ {driverId : driver_id,
          name     : name,
          points   : points @nocheck} ]};

The above example creates a duality view supporting JSON documents where the team object contain a field driver whose value is an array of nested objects that specify the drivers on the team:

{"_id" : 301, "name" : "Red Bull", "points" : 0, "driver" : [...]}