JSON relational duality views store documents in relational tables while applications read and write them as JSON. Designing the tables by hand takes time when the documents already exist. DBMS_JSON_DUALITY, the JSON-to-duality migrator, infers tables and a duality view from existing documents, generates the DDL, and imports the documents into them.
Code for This Guide
The main examples are in the examples/pkg-json folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_ddl := dbms_json_duality.infer_and_generate_schema(json('{
"tableNames" : ["SOURCE_COLLECTION"],
"viewNames" : ["NEW_VIEW"],
"outputFormat": "executable"}'));
execute immediate v_ddl;
dbms_json_duality.import_all(json('{"tableNames": [...], "viewNames": [...]}'));Migrate a Collection of Crew Documents
Three crew documents arrive in a JSON collection table. The migrator infers a table for them, creates a duality view, and imports the documents.
Example:
-- documents as another system delivers them, in a JSON collection table
create json collection table crew_docs;
insert into crew_docs
values (json('{"_id":1,"name":"Anil Rao","rank":"Captain","base":"DXB"}'));
insert into crew_docs
values (json('{"_id":2,"name":"Mei Lin","rank":"First Officer","base":"SIN"}'));
insert into crew_docs
values (json('{"_id":3,"name":"Omar Haddad","rank":"Captain","base":"DXB"}'));
commit;
declare
v_ddl clob;
begin
v_ddl := dbms_json_duality.infer_and_generate_schema(json('{
"tableNames" : ["CREW_DOCS"],
"viewNames" : ["CREW_DV"],
"useFlexFields": false,
"outputFormat" : "executable"}'));
dbms_output.put_line(v_ddl);
execute immediate v_ddl; -- create the table and the view
dbms_json_duality.import_all(json('{"tableNames": ["CREW_DOCS"],
"viewNames" : ["CREW_DV"]}'));
end;
/
select json_serialize(json_transform(data, remove '$._metadata')) as crew from crew_dv;Output:
JSON collection table CREW_DOCS created.
1 row inserted.
1 row inserted.
1 row inserted.
Commit complete.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE crew_dv_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
base varchar2(64),
name varchar2(64) /* UNIQUE */,
rank varchar2(64),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CREW_DV AS
crew_dv_root @insert @update @delete
{
"_id"
base
name
rank
}';
END;
PL/SQL procedure successfully completed.
CREW
_________________________________________________________________
{"_id":1,"base":"DXB","name":"Anil Rao","rank":"Captain"}
{"_id":2,"base":"SIN","name":"Mei Lin","rank":"First Officer"}
{"_id":3,"base":"DXB","name":"Omar Haddad","rank":"Captain"}- The generated PL/SQL block creates CREW_DV_ROOT, with a column per field and _id as the primary key, and the duality view CREW_DV over it, allowing insert, update, and delete.
- IMPORT_ALL copies the documents into the new table through the view.
- The view returns the same documents, now stored as rows; JSON_TRANSFORM removes the _metadata field the view adds.
The example drops the view and both tables afterward.
Things to Know
- Review the generated DDL before running it: the migrator guesses types and lengths from the sample documents.
- Nested objects and arrays become separate tables joined in the view.
- Set useFlexFields to true to keep fields the migrator did not map in flexible JSON columns.
Related Guides
Conclusion
DBMS_JSON_DUALITY infers relational tables and a duality view from existing JSON documents, generates the DDL, and imports the data. Use it to move document collections into relational storage while applications keep using JSON.
