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.
See my articles more often when you search Google.
