Inferring JSON Relational Duality Schema in Oracle 26ai

 
This post walks through a practical and very revealing example of how Oracle Database 26ai infers relational schemas and JSON Relational Duality Views from JSON documents — and how the behaviour changes significantly when you insert JSON using extended syntax.
 
We will use: 

•  A JSON Collection Table
• DBMS_JSON_DUALITY.INFER_AND_GENERATE_SCHEMA
• The same JSON document, inserted in two different ways 

The end result shows two very different relational models inferred by Oracle.
 
We start with a simple JSON document that includes an _id field using MongoDB-style $oid notation.
 
demo@ORA26AI> variable x varchar2(300)
demo@ORA26AI>
demo@ORA26AI> begin
  2    :x := q'# {
  3      "_id": {
  4        "$oid": "68d84c709ef2358f438a1cea"
  5      },
  6      "firstName": "fullName-ro",
  7      "type": "Prospect",
  8      "fullName": "fullName-ro",
  9      "birthDate": "1991-07-09",
 10      "nickname": "nickname-ro"
 11    } #';
 12    end;
 13*   /
 
PL/SQL procedure successfully completed.
 
demo@ORA26AI>
demo@ORA26AI> print x
 
X
-------------------------------------------
 {
    "_id": {
      "$oid": "68d84c709ef2358f438a1cea"
    },
    "firstName": "fullName-ro",
    "type": "Prospect",
    "fullName": "fullName-ro",
    "birthDate": "1991-07-09",
    "nickname": "nickname-ro"
  }
 
demo@ORA26AI>
 
We create a JSON collection table — the native storage abstraction for document-style JSON in Oracle
 
demo@ORA26AI> create json collection table my_demo;
 
JSON collection table MY_DEMO created.
 
Now Inserting JSON Without Extended Syntax
 
demo@ORA26AI> insert into my_demo values( json(:x) );
 
1 row inserted.
 
Now lets invoke dbms_json_duality API and the method infer_and_generate_schema to see what kind of relation tables are generated based on the documented persisted in the collection.
 
demo@ORA26AI> declare
  2     l_sql clob ;
  3     l_inputs json;
  4  begin
  5     l_inputs :=  json(' {"tableNames" :["MY_DEMO"], "useFlexFields" : false, "updatability" : true }');
  6     l_sql := dbms_json_duality.infer_and_generate_schema( l_inputs );
  7
  8     dbms_output.put_line('------------------ DDL Scripts ------------------------');
  9     dbms_output.put_line(l_sql);
 10  end;
 11* /
------------------ DDL Scripts ------------------------
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE my_demo__id(
   "$oid"  varchar2(64)  DEFAULT ON NULL SYS_GUID(),
   PRIMARY KEY("$oid")
)';
 
EXECUTE IMMEDIATE 'CREATE TABLE my_demo_root(
   type  varchar2(64)  DEFAULT ON NULL SYS_GUID(),
   nickname  varchar2(64)  /* UNIQUE */,
   full_name  varchar2(64)  /* UNIQUE */,
   birth_date  timestamp,
   first_name  varchar2(64)  /* UNIQUE */,
   "$oid_my_demo__id"  varchar2(64),
   PRIMARY KEY(type)
)';
 
EXECUTE IMMEDIATE 'ALTER TABLE my_demo_root
ADD CONSTRAINT fk_my_demo_root_to_my_demo__id FOREIGN KEY ("$oid_my_demo__id") REFERENCES my_demo__id("$oid") DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_my_demo_root_to_my_demo__id_index ON my_demo_root("$oid_my_demo__id")';
 
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW MY_DEMO_DUALITY AS
my_demo_root @insert @update @delete
{
  _id: my_demo__id @insert @update @object
  {
    "$oid"
  }
  type
  fullName: full_name
  nickname
  birthDate: birth_date
  firstName: first_name
}';
END;
 
 
 
PL/SQL procedure successfully completed.
 
 
Oracle produces two tables:
 
ID Table (MY_DEMO__ID) 

• $oid becomes a column
• A surrogate primary key is created 
  

Root Table (MY_DEMO_ROOT) 

• Business attributes live here
• A foreign key references the ID table

 
MY_DEMO__ID
 └── "$oid" (PK)
 
MY_DEMO_ROOT
 ├── type (PK)
 ├── first_name
 ├── full_name
 ├── nickname
 ├── birth_date
 └── "$oid_my_demo__id" (FK)
 
The inferred duality view explicitly models _id as a nested object:
 
_id: my_demo__id @object
{
  "$oid"
}
 
Because the JSON was inserted without extended syntax: 

• _id is treated as application data
• Oracle normalizes it into a separate relational table
• Referential integrity is inferred automatically 

 
Now lets clean up the data from the collection and re-Insert JSON With Extended Syntax
 
demo@ORA26AI> truncate table my_demo;
 
Table MY_DEMO truncated.
 
demo@ORA26AI> insert into my_demo values( json(:x extended) );
 
1 row inserted.
 
demo@ORA26AI> declare
  2     l_sql clob ;
  3     l_inputs json;
  4  begin
  5     l_inputs :=  json(' {"tableNames" :["MY_DEMO"], "useFlexFields" : false, "updatability" : true }');
  6     l_sql := dbms_json_duality.infer_and_generate_schema( l_inputs );
  7
  8     dbms_output.put_line('------------------ DDL Scripts ------------------------');
  9     dbms_output.put_line(l_sql);
 10  end;
 11* /
------------------ DDL Scripts ------------------------
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE my_demo_root(
   "_id"  raw(64)  DEFAULT ON NULL SYS_GUID(),
   type  varchar2(64)  /* UNIQUE */,
   nickname  varchar2(64)  /* UNIQUE */,
   full_name  varchar2(64)  /* UNIQUE */,
   birth_date  timestamp,
   first_name  varchar2(64)  /* UNIQUE */,
   PRIMARY KEY("_id")
)';
 
 
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW MY_DEMO_DUALITY AS
my_demo_root @insert @update @delete
{
  "_id" @cast(as: id)
  type
  fullName: full_name
  nickname
  birthDate: birth_date
  firstName: first_name
}';
END;
 
 
 
PL/SQL procedure successfully completed.
 
With extended JSON: 

• _id is recognized as a document identifier
• $oid is no longer treated as user data

Oracle now generates only one table.
 
MY_DEMO_ROOT
 ├── _id (RAW, PK)
 ├── type
 ├── first_name
 ├── full_name
 ├── nickname
 └── birth_date
 
No separate ID table. No foreign key.
 
Duality View
 
"_id" @cast(as: id)
 
The _id field is now mapped directly to the table primary key.
 
Choosing json(:x) vs json(:x extended) affects: 

•  Physical schema design
• Key modeling
• Join complexity
• Duality view shape 

Schema inference is not just about the JSON shape — it’s about intent.
 
Oracle 26ai uses extended JSON semantics to distinguish between: 

• Document identity
• Application-level attributes 

Understanding this distinction allows you to design cleaner, more efficient JSON-relational architectures with duality views.