The JSON-To-Duality Migrator is a new tool in Oracle Database 23ai that can migrate one or more existing sets of JSON documents to JSON-relational duality views. The migrator can be used for database migration from any document database using JSON documents or build a new application using JSON document as the migrator will automatically creating the necessary duality views.
Migration of MongoDB collections to Oracle JSON Autonomous Database can also be done using the “Oracle Database API for MongoDB” (click on the image below):
This blog post shows an example of how this can be done using the new migrator which has two components:
Converter: Create the database objects needed to support the original JSON documents: duality views and their underlying tables and indexes.
Importer: Import Oracle Database JSON-type document sets that correspond to the original external documents into the duality views created by the converter.
Here is what we will do:
Create a native JSON collection
Import the data from the JSON files extracted from another database into Oracle Database 23.4
Run the JSON-To-Duality Migrator (both converter and importer)
Validate the newly created objects (tables and duality views)
1. Let us first create the collection under the ADMIN schema:
CREATE JSON COLLECTION TABLE ADMIN.CONF_SCHEDULE;
2. Next, consider the following collection which was extracted from a different document database. This is simply the conference schedule of 4 people (Jill, John, Ronan and Julian) in JSON format:
[
{
“_id”: 1,
“name”: “Jill”,
“schedule”: [
{
“scheduleId”: 1,
“sessionId”: 1,
“name”: “JSON and SQL”,
“location”: “Room 1”,
“speakerId”: 1,
“speaker”: “Jenny”
},
{
“scheduleId”: 2,
“sessionId”: 2,
“name”: “PL/SQL or Javascript”,
“location”: “Room 2”,
“speakerId”: 1,
“speaker”: “Julian”
},
{
“scheduleId”: 4,
“sessionId”: 3,
“name”: “Oracle on IPhone”,
“location”: “Room 1”,
“speakerId”: 2,
“speaker”: “Jenny”
},
{
“scheduleId”: 6,
“sessionId”: 5,
“name”: “MongoDB API Internals”,
“location”: “Room 4”,
“speakerId”: 4,
“speaker”: “Julian”
}
]},
{
“_id”: 2,
“name”: “John”,
“schedule”: [
{
“scheduleId”: 3,
“sessionId”: 2,
“name”: “PL/SQL or Javascript”,
“location”: “Room 2”,
“speakerId”: 1,
“speaker”: “Jenny”
},
{
“scheduleId”: 7,
“sessionId”: 5,
“name”: “MongoDB API Internals”,
“location”: “Room 4”,
“speakerId”: 4,
“speaker”: “Julian”
}
]},
{
“_id”: 4,
“name”: “Ronan”,
“schedule”: []
},
{
“_id”: 3,
“name”: “Julian”,
“schedule”: [
{
“scheduleId”: 5,
“sessionId”: 4,
“name”: “JSON Duality View”,
“location”: “Room 3”,
“speakerId”: 3,
“speaker”: “Ralf”
}
]}
]
Let us import it (for example) using MongoDB Compass. Alternatively, you can just insert the data into the collection.
We now have the JSON data in an native collection.
3. Let us now run the JSON-To-Duality Migrator (both converter and importer):
SET SERVEROUTPUT ON
SET LINESIZE 10000
DECLARE
schema_sql clob;
BEGIN
schema_sql :=
dbms_json_duality.infer_and_generate_schema(
json(‘{“tableNames” : [ “CONF_SCHEDULE” ],
“useFlexFields” : false,
“updatability” : false,
“sourceSchema” : “ADMIN”}’));
dbms_output.put_line(‘DDL Script: ‘);
dbms_output.put_line(schema_sql);
execute immediate schema_sql;
dbms_json_duality.import(table_name => ‘CONF_SCHEDULE’,
view_name => ‘CONF_SCHEDULE_DUALITY’);
END;
/
4. Let us finally validate and check what happened under the covers:
DDL Script:
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE conf_schedule_root(
“_id” number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(32) /* UNIQUE */,
PRIMARY KEY(“_id”)
)’;
EXECUTE IMMEDIATE ‘CREATE TABLE conf_schedule_schedule(
name varchar2(32),
speaker varchar2(32),
location varchar2(32),
session_id number,
speaker_id number,
schedule_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
“_id_conf_schedule_root” number,
PRIMARY KEY(schedule_id)
)’;
EXECUTE IMMEDIATE ‘ALTER TABLE conf_schedule_schedule
ADD CONSTRAINT fk_conf_schedule_schedule_to_conf_schedule_root FOREIGN KEY (“_id_conf_schedule_root”) REFERENCES conf_schedule_root(“_id”)’;
EXECUTE IMMEDIATE ‘CREATE INDEX IF NOT EXISTS fk_conf_schedule_schedule_to_conf_schedule_root_index ON conf_schedule_schedule(“_id_conf_schedule_root”)’;
EXECUTE IMMEDIATE ‘CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CONF_SCHEDULE_DUALITY AS
conf_schedule_root
{
“_id”
name
schedule: conf_schedule_schedule
{
name
speaker
location
sessionId: session_id
speakerId: speaker_id
scheduleId: schedule_id
}
}’;
END;
PL/SQL procedure successfully completed.
SQL>
Now, run these queries to validate and witness what the migrator did under the covers:
select json_serialize(data pretty) from “ADMIN”.”CONF_SCHEDULE_DUALITY”;
select * from dba_objects where owner=’ADMIN’ and object_name like ‘%SCHEDULE%’; — note also the newly created FK
select * from ADMIN.CONF_SCHEDULE_SCHEDULE;
select * from ADMIN.CONF_SCHEDULE_ROOT;
In MongoDB Compass after refreshing the databases, click on the CONF_SCHEDULE_DUALITY collection under the ADMIN database:
In mongosh, verify the number of documents in the CONF_SCHEDULE_DUALITY collection:
admin> db.CONF_SCHEDULE_DUALITY.countDocuments({});
4
What we have now is the document database data in an Oracle database 23.4 (in relational form) with the JSON duality views on top of the tables.
Here are some more details on the concepts used and mentioned above:
The converter is composed of these 3 PL/SQL functions in the package DBMS_JSON_DUALITY:
infer_schema: Infer a relational schema that represents the documents in the existing document sets.
Input: A JSON object whose members specify configuration parameters for the inference operation
Output: a JSON Schema document that specifies the inferred relational schema. If no such schema can be found then an error is raised saying that the converter can’t create duality views corresponding to the input document sets.
generate_schema: Produce the SQL data-definition language (DDL) scripts to generate the necessary duality views and their underlying tables and indexes.
Input: the JSON schema output from function infer_schema.
Output: DDL scripts to create the needed database objects.
infer_and_generate_schema: Do infer_schema and generate_schema together.
Input: same as infer_schema.
Output: same as generate_schema.
The importer is the PL/SQL procedure DMBS_JSON_DUALITY.import which populates the duality view created by the converter with the documents from the corresponding input document set. Actually, it populates the relational data needed to support such documents. Dually views are not physical objects.
You probably noticed the 2 parameters useFlexFields” and updatability. Here is what they are meant for:
updatability is an optional Boolean value determining whether the duality views to be generated are to be updatable (true) or not (false). When true, annotations are set for maximum updatability of each view. When false all of the views created are read-only. The default value is true.
useFlexFields is another optional Boolean value determining whether flex columns are to be added to the tables underlying the duality views. Flex columns are used to store unrecognized fields in an incoming document to be inserted or updated. When useFlexFields is true, for each duality view <view-name>, a flex column named ora$<view-name>_flex is added to each table that directly underlies the top-level fields of an object in the supported documents. The default value is true.
Flex fields, outliers (high-entropy fields), etc. will be a subject of another blog post.
Read MoreJulian Dontcheff’s Database Blog
