21.9 Migrating To Duality, Simplified Recipe

By ignoring whether an input field occurs rarely, or with a rarely used type, it's easier to migrate to JSON-relational duality. Handling such outlier cases can complicate the migration process.

But handling such cases can allow finer-degree normalization, and it can help find anomalies in your data that could represent bugs. This topic is about the simpler approach. Subsequent topics go into details that help you better understand and configure the migrator.

By default, the converter treats fields that occur in less than 5% of the documents of a document set as occurrence outliers, and field occurrences that have a given type in less than 5% of the documents as type-occurrence outliers, or type outliers. You can change the values of these thresholds using configuration fields minFieldFrequency and minTypeFrequency, respectively.

To show the reporting and handling of outliers, in the student-teacher-course example we generally use 25% for minFieldFrequency and 15% for minTypeFrequency, because the document sets are small. But for the simplified recipe used in this topic we set both of them to zero percent, so no fields are considered outliers.

Besides setting these two thresholds to zero, the default behavior of the migrator is what's illustrated here. This simplified recipe — default behavior except no outliers — isn't a bad way to begin whenever you migrate an application. And in many cases it will also be just what you need in the end.

By setting the minimum frequency thresholds to zero percent we configure the converter to accept as much as possible of the input data to be migrated, as is, at the possible cost of sacrificing maximal normalization. Our input student-teacher-course data contains some fields that we might ultimately want to treat as outliers, but there's no attempt in this topic to deal with them specially.

The fields that we normally treat as outliers in the rest of the migrator documentation are handled in these ways in this topic:

  • The Notes occurrence in the course document for MATH101 (Algebra) isn't removed, even though it occurs in only one (20%) of the documents.

  • The age occurrence with string value "Nineteen", in the student document for Luis F., isn't converted to the number 19 so that its type agrees with the age occurrences (numbers) in the other nine documents (90%). Nor is a schema-inference validation error reported for this occurrence.

Instead, such input data, which could otherwise be considered problematic, is simply kept as is.

It's important to point out that outlier fields are not the only problems that migration might uncover. Even using the simplified recipe presented here it's possible that the importer can raise errors. A good example of that is two document sets that contradict each other, making it impossible to reconcile them without fixing the input data — for example, a course document says that Natalie C. teaches course MATH101 and a teacher document says that Abdul J. teaches it. See Errors That Migrator Configuration Alone Can't Fix . (The migrator can help you discover some data coherency problems such as this, even if you're not migrating any data!)

Only you know, for your application, whether any particular data is an anomaly, according to your use of it. For example, only you know whether a rare type for a field, such as the single occurrence of string "Nineteen" for a student age field (whose value is usually a number), is normal or abnormal. Wanting maximum respect of your input data is the use case explored here with the simplified recipe. This is also an approach you might want to use generally, as a first step in migrating document sets, because it can quickly show you most of what's what.

The starting point for the migration is the three input document sets stored in Oracle Database transfer tables, as covered in Before Using the Converter (1): Create Database Document Sets . The input documents are shown both there and (more compactly) in School Administration Example, Migrator Input Documents .

We first use PL/SQL function DBMS_JSON_DUALITY.infer_schema, followed by function DBMS_JSON_DUALITY.generate_schema, to produce the SQL data-definition (DDL) code that creates (1) the duality views, (2) their underlying tables, (3) foreign-key constraints and indexes on the tables, and (4) triggers to create document-identifier fields _id for duality views where it doesn't already exist for the document set. The DDL code also adds top-level document-identifier field _id, because the input data doesn't already have it.

We then run that generated DDL code, creating those database objects.

Example 21-9 INFER_SCHEMA and GENERATE_SCHEMA with Zero Frequency Thresholds: No Outliers

In this example, PL/SQL function DBMS_JSON_DUALITY.infer_schema returns the JSON schema representing the inferred duality views and their underlying tables and indexes in JSON-type variable er_schema, which is passed to PL/SQL function DBMS_JSON_DUALITY.generate_schema. The output from generate_schema, SQL DDL code to create those database objects, is invoked using EXECUTE IMMEDIATE.

Configuration fields minFieldFrequency and minTypeFrequency are both set to zero for the schema inference by function infer_schema. This means that no fields in the input JSON data are to be considered outliers. 

DECLARE
  er_schema   JSON;
  schema_sql  CLOB;
BEGIN
  er_schema :=
   DBMS_JSON_DUALITY.infer_schema(
     JSON('{"tableNames"        : [ "STUDENT_TAB",
                                    "TEACHER_TAB",
                                    "COURSE_TAB"],
            "viewNames"         : [ "STUDENT",
                                    "TEACHER",
                                    "COURSE" ],
            "minFieldFrequency" : 0,
            "minTypeFrequency"  : 0}'));
  schema_sql := DBMS_JSON_DUALITY.generate_schema(er_schema);
  EXECUTE IMMEDIATE schema_sql;
END;
/

Function infer_schema produces a JSON schema that describes the duality views and their tables. In this case, the schema shows that all of the input-data fields will be supported by the duality views.

Example 21-10 JSON Schema from INFER_SCHEMA for Duality Views with No Outliers

{"tables"        :
  [ {"title"        : "map_course_root_to_student_root",
     "dbObject"     : "map_course_root_to_student_root",
     "type"         : "object",
     "dbObjectType" : "table",
     "dbMapTable"   : true,
     "properties"   : {"map_course_id"  : {"sqlType"   : "varchar2",
                                           "maxLength" : 64,
                                           "nullable"  : false},
                       "map_student_id" : {"sqlType" : "number",
                                           "nullable" : false}},
                      "required"     : [ "map_course_id", "map_student_id" ],
                      "dbPrimaryKey" : [ "map_course_id",
                                         "map_student_id"],
                      "dbForeignKey" : [ {"map_course_id"  : {"dbObject" : "course_root",
                                                              "dbColumn" : "course_id"}},
                                         {"map_student_id" : {"dbObject" : "student_root",
                                                              "dbColumn" : "student_id"}} ]},
    {"title"        : "teacher_root",
     "dbObject"     : "teacher_root",
     "type"         : "object",
     "dbObjectType" : "table",
     "properties"   : {"_id"          : {"sqlType"   : "number", "nullable" : false},
                       "name"         : {"sqlType"   : "varchar2",
                                         "maxLength" : 64,
                                         "nullable"  : true,
                                         "unique"    : false},
                       "salary"       : {"sqlType"  : "number",
                                         "nullable" : true,
                                         "unique"   : false},
                       "department"   : {"sqlType"   : "varchar2",
                                         "maxLength" : 64,
                                         "nullable"  : true,
                                         "unique"    : false},
                       "phone_number" : {"sqlType"  : "json",
                                         "nullable" : true,
                                         "unique"   : false}},
     "required"     : [ "_id" ],
     "dbPrimaryKey" : [ "_id" ]},
    {"title"        : "course_root",
     "dbObject"     : "course_root",
     "type"         : "object",
     "dbObjectType" : "table",
     "properties"   : {"name"             : {"sqlType"   : "varchar2",
                                             "maxLength" : 64,
                                             "nullable"  : true,
                                             "unique"    : false},
                       "notes"            : {"sqlType"   : "varchar2",
                                             "maxLength" : 64,
                                             "nullable"  : true,
                                             "unique"    : false},
                       "course_id"        : {"sqlType"   : "varchar2",
                                             "maxLength" : 64,
                                             "nullable"  : false},
                       "credit_hours"     : {"sqlType"  : "json",
                                             "nullable" : true,
                                             "unique"   : false},
                       "class_type"       : {"sqlType"   : "varchar2",
                                             "maxLength" : 64,
                                             "nullable"  : true,
                                             "unique"    : false},
                       "avg_grade"        : {"sqlType"  : "json",
                                             "nullable" : true,
                                             "unique"   : false},
                       "_id_teacher_root" : {"sqlType"  : "number",
                                             "nullable" : true,
                                             "unique"   : false}},
     "required"     : [ "course_id" ],
     "dbPrimaryKey" : [ "course_id" ],
     "dbForeignKey" : [ {"_id_teacher_root" : {"dbObject" : "teacher_root",
                                               "dbColumn" : "_id"}} ]},
    {"title"        : "student_root",
     "dbObject"     : "student_root",
     "type"         : "object",
     "dbObjectType" : "table",
     "properties"   : {"age"        : {"sqlType" : "json",
                                       "nullable" : true,
                                       "unique" : false},
                       "name"       : {"sqlType" : "varchar2",
                                       "maxLength" : 64,
                                       "nullable" : true,
                                       "unique" : false},
                       "advisor_id" : {"sqlType" : "number",
                                       "nullable" : true,
                                       "unique" : false},
                       "student_id" : {"sqlType" : "number",
                                       "nullable" : false},
                       "dorm_id"    : {"sqlType" : "number",
                                       "nullable" : true,
                                       "unique" : false}},
     "required"     : [ "student_id" ],
     "dbPrimaryKey" : [ "student_id" ],
     "dbForeignKey" : [ {"advisor_id" : {"dbObject" : "teacher_root",
                                         "dbColumn" : "_id"}},
                        {"dorm_id"    : {"dbObject" : "student_dormitory",
                                         "dbColumn" : "dorm_id"}} ]},
    {"title"        : "student_dormitory",
     "dbObject"     : "student_dormitory",
     "type"         : "object",
     "dbObjectType" : "table",
     "properties"   : { "dorm_id"   : {"sqlType" : "number",
                                       "nullable" : false},
                        "dorm_name" : {"sqlType" : "varchar2",
                                       "maxLength" : 64,
                                       "nullable" : true,
                                       "unique" : false}},
     "required"     : [ "dorm_id" ],
     "dbPrimaryKey" : [ "dorm_id" ]} ],
 "views"         : [ {"title"               : "STUDENT",
                      "dbObject"            : "STUDENT",
                      "dbObjectType"        : "dualityView",
                      "dbObjectProperties"  : [ "insert", "update", "delete", "check" ],
                      "dbMappedTableObject" : "student_root",
                      "type"                : "object",
                      "properties"          :
                        {"_id"          : {"type"              : "number",
                                           "dbAssigned"        : true,
                                           "dbFieldProperties" : [ "check" ],
                                           "dbObject"          : "student_root",
                                           "dbColumn"          : "student_id"},
                         "dbPrimaryKey" : [ "_id" ],
                         "age"          : {"type"              : [ "number",
                                                                   "string",
                                                                   "null" ],
                                           "dbFieldProperties" : [ "update", "check" ],
                                           "dbObject"          : "student_root",
                                           "dbColumn"          : "age"},
                         "name"         : {"type"              : [ "string", "null" ],
                                           "maxLength"         : 64,
                                           "dbFieldProperties" : [ "update", "check" ],
                                           "dbObject"          : "student_root",
                                           "dbColumn"          : "name"},
                         "courses"      :
                           {"type"   : "array",
                            "items"  : {"type"                : "object",
                                        "dbMappedTableObject" : "course_root",
                                        "properties" :
                                          {"dbPrimaryKey"     : [ "ora$mapCourseId",
                                                                  "ora$mapStudentId" ],
                                           "ora$mapCourseId"  :
                                             {"type"              : "string",
                                              "maxLength"         : 64,
                                              "dbAssigned"        : true,
                                              "dbFieldProperties" : [ "check" ]},
                                           "ora$mapStudentId" :
                                              {"type" : "number",
                                               "dbAssigned" : true,
                                               "dbFieldProperties" : [ "check" ] },
                                           "name"             :
                                             {"type"              : [ "string",
                                                                      "null" ],
                                              "maxLength"         : 64,
                                              "dbFieldProperties" : [ "update", "check" ],
                                              "dbObject"          : "course_root",
                                              "dbColumn"          : "name"},
                                           "avgGrade"         :
                                             {"type"              : [ "number",
                                                                      "string",
                                                                      "null" ],
                                              "dbFieldProperties" : [ "update", "check" ],
                                              "dbObject"          : "course_root",
                                              "dbColumn"          : "avg_grade"},
                                           "courseNumber"     :
                                             {"type"              : "string",
                                              "maxLength"         : 64,
                                              "dbFieldProperties" : [ "check" ],
                                              "dbObject"          : "course_root",
                                              "dbColumn"          : "course_id"}},
                                        "required"   : [ "ora$mapCourseId",
                                                         "ora$mapStudentId",
                                                         "courseNumber" ]}},
                         "advisorId" : {"type"              : [ "number", "null" ],
                                        "dbFieldProperties" : [ "update", "check" ],
                                        "dbObject"          : "student_root",
                                        "dbColumn"          : "advisor_id"},
                         "dormitory" : {"type"                : "object",
                                        "dbMappedTableObject" : "student_dormitory",
                                        "properties" :
                                          {"dormId"   :
                                             {"type"              : "number",
                                              "dbFieldProperties" : [ "check" ],
                                              "dbObject"          : "student_dormitory",
                                              "dbColumn"          : "dorm_id"},
                                           "dormName" :
                                             {"type"              : [ "string", "null" ],
                                              "maxLength"         : 64,
                                              "dbFieldProperties" : [ "update", "check" ],
                                             "dbObject"           : "student_dormitory",
                                             "dbColumn"           : "dorm_name"}},
                                        "required"  : [ "dormId" ]},
                         "studentId" : {"dbFieldProperties" : [ "computed" ]}}},
                     {"title"               : "COURSE",
                      "dbObject"            : "COURSE",
                      "dbObjectType"        : "dualityView",
                      "dbObjectProperties"  : [ "insert", "update", "delete", "check" ],
                      "dbMappedTableObject" : "course_root",
                      "type"                : "object",
                      "properties" :
                        {"_id"         : { "type"              : "string",
                                           "maxLength"         : 64,
                                           "dbAssigned"        : true,
                                           "dbFieldProperties" : [ "check" ],
                                           "dbObject"          : "course_root",
                                           "dbColumn"          : "course_id"},
                         "dbPrimaryKey" : [ "_id" ],
                         "name"         : {"type"              : [ "string", "null" ],
                                           "maxLength"         : 64,
                                           "dbFieldProperties" : [ "update", "check" ],
                                           "dbObject"          : "course_root",
                                           "dbColumn"          : "name"},
                         "Notes"        : {"type"              : [ "string", "null" ],
                                           "maxLength"         : 64,
                                           "dbFieldProperties" : ["update", "check" ],
                                           "dbObject"          : "course_root",
                                           "dbColumn"          : "notes"},
                         "teacher"      :
                           {"type"                : "object",
                            "dbMappedTableObject" : "teacher_root",
                            "properties" :
                            {"name"      : {"type"              : [ "string", "null" ],
                                            "maxLength" : 64,
                                            "dbFieldProperties" : [ "update", "check" ],
                                            "dbObject"          : "teacher_root",
                                            "dbColumn"          : "name"},
                             "teacherId" : {"type"              : "number",
                                            "dbFieldProperties" : [ "check" ],
                                            "dbObject"          : "teacher_root",
                                            "dbColumn"          : "_id"}},
                            "required"   : [ "teacherId" ]},
                         "courseId"     : {"dbFieldProperties" : [ "computed" ]},
                         "students"     :
                           {"type" : "array",
                            "items" :
                              {"type" : "object",
                               "dbMappedTableObject" : "student_root",
                               "properties" :
                                 {"dbPrimaryKey"     : [ "ora$mapCourseId",
                                                         "ora$mapStudentId" ],
                                  "ora$mapCourseId"  : {"type"              : "string",
                                                        "maxLength"         : 64,
                                                        "dbAssigned"        : true,
                                                        "dbFieldProperties" : [ "check" ]},
                                  "ora$mapStudentId" : {"type"              : "number",
                                                        "dbAssigned"        : true,
                                                        "dbFieldProperties" : [ "check" ]},
                                  "name"             :
                                    {"type"              : [ "string", "null" ],
                                     "maxLength"         : 64,
                                     "dbFieldProperties" : [ "update", "check" ],
                                     "dbObject"          : "student_root",
                                     "dbColumn"          : "name"},
                                  "studentId"        : {"type"              : "number",
                                                        "dbFieldProperties" : [ "check" ],
                                                        "dbObject"          : "student_root",
                                                        "dbColumn"          : "student_id"}},
                               "required"   : [ "ora$mapCourseId",
                                                "ora$mapStudentId",
                                                "studentId" ]}},
                         "creditHours"  :
                           {"type"              : [ "number", "string", "null" ],
                            "dbFieldProperties" : [ "update", "check" ],
                            "dbObject"          : "course_root",
                            "dbColumn"          : "credit_hours"}}},
                     {"title"               : "TEACHER",
                      "dbObject"            : "TEACHER",
                      "dbObjectType"        : "dualityView",
                      "dbObjectProperties"  : [ "insert", "update", "delete", "check" ],
                      "dbMappedTableObject" : "teacher_root",
                      "type"                : "object",
                      "properties" :
                        {"_id"             : {"type"               : "number",
                                              "dbFieldProperties"  : [ "check" ],
                                              "dbObject"           : "teacher_root",
                                              "dbColumn"           : "_id"},
                         "name"            : {"type"              : [ "string", "null" ],
                                              "maxLength"         : 64,
                                              "dbFieldProperties" : [ "update", "check" ],
                                              "dbObject"          : "teacher_root",
                                              "dbColumn"          : "name"},
                         "salary"          : {"type"              : [ "number", "null" ],
                                              "dbFieldProperties" : [ "update", "check" ],
                                              "dbObject"          : "teacher_root",
                                              "dbColumn"          : "salary"},
                         "department"      : {"type"              : [ "string", "null" ],
                                              "maxLength"         : 64,
                                              "dbFieldProperties" : [ "update", "check" ],
                                              "dbObject"          : "teacher_root",
                                              "dbColumn"          : "department"},
                         "phoneNumber"     :
                           {"type"              : [ "string", "array", "null" ],
                            "dbFieldProperties" : [ "update", "check" ],
                            "dbObject"          : "teacher_root",
                            "dbColumn"          : "phone_number"},
                         "coursesTaught"  : 
                           {"type"  : "array",
                            "items" :
                              {"type"                : "object",
                               "dbMappedTableObject" : "course_root",
                               "properties" :
                                 {"name"     :  {"type"              : [ "string", "null" ],
                                                 "maxLength"         : 64,
                                                 "dbFieldProperties" : [ "update", "check" ],
                                                 "dbObject"          : "course_root",
                                                 "dbColumn"          : "name"},
                                  "courseId"  : {"type"              : "string",
                                                 "maxLength"         : 64,
                                                 "dbFieldProperties" : [ "check" ],
                                                 "dbObject"          : "course_root",
                                                 "dbColumn"          : "course_id"},
                                  "classType" : {"type"              : [ "string", "null" ],
                                                 "maxLength"         : 64,
                                                 "dbFieldProperties" : [ "update", "check" ],
                                                 "dbObject"          : "course_root",
                                                 "dbColumn"          : "class_type"}},
                               "required"   : [ "courseId" ]}},
                         "studentsAdvised" :
                           {"type"  : "array",
                            "items" :
                              {"type"                : "object",
                               "dbMappedTableObject" : "student_root",
                               "properties" :
                                 {"name"      : {"type"              : [ "string", "null" ],
                                                 "maxLength"         : 64,
                                                 "dbFieldProperties" : [ "update", "check" ],
                                                 "dbObject"          : "student_root",
                                                 "dbColumn"          : "name"},
                                  "dormId"    : {"type"              : [ "number", "null" ],
                                                 "dbFieldProperties" : [ "update", "check" ],
                                                 "dbObject"          : "student_root",
                                                 "dbColumn"          : "dorm_id"},
                                  "studentId" : {"type"              : "number",
                                                 "dbFieldProperties" : [ "check" ],
                                                 "dbObject"          : "student_root",
                                                 "dbColumn"          : "student_id"}},
                               "required"   : [ "studentId" ]}}}} ],
 "configOptions" : {"outputFormat"  : "executable",
                    "useFlexFields" : true}}

General observations:

  • There are two parts to the schema: (1) a specification of the tables underlying the duality views (field tables) and (2) a specification of the duality views themselves (field views).

  • The SQL data types of columns in the tables are specified by field sqlType. For example, column department has SQL type VARCHAR2 because the value of document field department is always a JSON string.

  • JSON data type is used for columns phone_number, credit_hours, avg_grade, and age, because the corresponding fields (phoneNumber, creditHours, avgGrade, and age) in the input documents have mixed type. There's no single SQL scalar type that can be used for such a field.

  • Table map_course_root_to_student_root is a mapping table between tables course_root and student_root.

  • All of the columns exceptFoot 1 identifying columns (primary-key columns in the example data) are flagged with nullable = true, which means that their values can be (SQL) NULL. Fields corresponding to nullable columns (1) need not be present in a given document, and (2) when present, can have (JSON) null values.

There's a lot more information in that schema. Let's just point out some of it for now, using the schema of the student view and its documents as an example.

  • For document-identifier field _id, schema field dbAssigned tells us that _id is added automatically to the documents for each duality view — it isn't present in the input data. And schema field dbColumn tells us that the _id value is stored in column student_id.

  • Elsewhere, schema field dbAssigned tells us that fields ora$mapCourseId and ora$mapStudentId are also added automatically to the student documents.

  • The (singleton array) value of schema field dbPrimaryKey tells us that document field _id corresponds to the only identifying (primary key) column of the duality view; field _id is the document identifier.

  • In the input student documents, field studentId is the document identifier. That top-level field is supported by the student duality view, but its value is generated by the view, not stored. The string "computed" in the array value of schema field dbFieldProperties tells us this. (Its value is in fact taken from the _id value in column student_id.)

  • Schema field dbMappedTableObject tells us that (1) table student_root is the root table underlying the student view, (2) table course_root underlies the array value of field courses, table student_dormitory underlies the fields in the object value of field dormitory.

Function generate_schema accepts as input a JSON schema such as the one produced by function infer_schema. In particular, this means that you can edit the JSON schema that infer_schema produces, to influence what generate_schema does. You can do this by hand-editing or by using SQL/JSON function json_transform. The following example illustrates the method of editing the JSON schema:

Example 21-11 Using JSON_TRANSFORM To Edit Inferred JSON Schema

As one example of modifying the JSON schema returned by DBMS_JSON_DUALITY.infer_schema, we change the maxLength value for a column from 64 to 100.

We assume here that the value of PL/SQL variable er_schema is the schema returned by DBMS_JSON_DUALITY.infer_schema, as in the example "INFER_SCHEMA and GENERATE_SCHEMA with Zero Frequency Thresholds: No Outliers". This json_transform code changes that schema, to maximum length of the name field of a student document to 100, saving the transformed value back into variable er_schema. The updated variable can then be passed to DBMS_JSON_DUALITY.generate_schema. 

SELECT json_transform(
         er_schema,
         SET '$.tables[3].properties.name.maxLength' = 100)
  INTO er_schema FROM dual;

The left-hand side of this SET operation is a SQL/JSON path expression. The schema specifying table student_root is the fourthFoot 2 entry in array tables of the overall JSON schema. Top-level field name for student documents is specified as a child of schema field properties, and field maxLength is a child of field name. (See Oracle SQL Function JSON_TRANSFORM in Oracle Database JSON Developer’s Guide.)

In the topic Migrating To Duality, Simplified Recipe, the example in "DDL Code from GENERATE_SCHEMA for No-Outlier Use Case" shows the DDL code produced by function generate_schema in the example "INFER_SCHEMA and GENERATE_SCHEMA with Zero Frequency Thresholds: No Outliers", which indicates that all input-data fields are supported.

Example 21-12 DDL Code from GENERATE_SCHEMA for No-Outlier Use Case

This example draws from the other examples in this topic. Function DBMS_JSON_DUALITY.generate_schema, produces the generated DDL code shown here if passed the JSON schema in the example "JSON Schema from INFER_SCHEMA for Duality Views with No Outliers", which is returned by function infer_schema (as shown in the example "INFER_SCHEMA and GENERATE_SCHEMA with Zero Frequency Thresholds: No Outliers", and using this as input.

(If instead it were passed the schema resulting from the modification in the example "Using JSON_TRANSFORM To Edit Inferred JSON Schema", then the only change here would be that column student_root.name would have a maxLength of 100 instead of 64.)

Because the value of configuration field outputFormat is "executable" (by default), the generated DDL code uses EXECUTE IMMEDIATE for its statements.

The triggers created by the DDL code add top-level field _id to each document of a view, giving it the value of the corresponding primary-key field in each case. For example, for a student document it gives the added _id field the value of input field studentId. 

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
   dorm_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   dorm_name  varchar2(64),
   ora$student_flex  JSON(Object),
   PRIMARY KEY(dorm_id)
)';

EXECUTE IMMEDIATE 'CREATE TABLE map_course_root_to_student_root(
   map_course_id  varchar2(64)  DEFAULT ON NULL SYS_GUID(),
   map_student_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   ora$student_flex  JSON(Object),
   ora$course_flex  JSON(Object),
   PRIMARY KEY(map_course_id,map_student_id)
)';

EXECUTE IMMEDIATE 'CREATE TABLE student_root(
   age  json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
   name  varchar2(64),
   dorm_id  number,
   advisor_id  number,
   student_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   ora$student_flex  JSON(Object),
   ora$teacher_flex  JSON(Object),
   PRIMARY KEY(student_id)
)';

EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
   "_id"  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name  varchar2(64),
   salary  number,
   department  varchar2(64),
   phone_number  json VALIDATE ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
   ora$course_flex  JSON(Object),
   ora$teacher_flex  JSON(Object),
   PRIMARY KEY("_id")
)';

EXECUTE IMMEDIATE 'CREATE TABLE course_root(
   name  varchar2(64),
   notes  varchar2(64),
   avg_grade  json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
   course_id  varchar2(64)  DEFAULT ON NULL SYS_GUID(),
   class_type  varchar2(64),
   credit_hours  json VALIDATE ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
   "_id_teacher_root"  number,
   ora$course_flex  JSON(Object),
   ora$teacher_flex  JSON(Object),
   PRIMARY KEY(course_id)
)';

EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_course_root FOREIGN KEY (map_course_id) REFERENCES course_root(course_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_student_root FOREIGN KEY (map_student_id) REFERENCES student_root(student_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_teacher_root FOREIGN KEY (advisor_id) REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory FOREIGN KEY (dorm_id) REFERENCES student_dormitory(dorm_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_course_root_index ON map_course_root_to_student_root(map_course_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_student_root_index ON map_course_root_to_student_root(map_student_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_teacher_root_index ON student_root(advisor_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_student_dormitory_index ON student_root(dorm_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_teacher_root_index ON course_root("_id_teacher_root")';

EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
  _id : student_id
  age
  name
  courses: map_course_root_to_student_root @insert @update @delete @array
  {
    ora$mapCourseId: map_course_id
    ora$mapStudentId: map_student_id
    ora$student_flex @flex
    course_root @unnest @insert @update @object
    {
      name
      avgGrade: avg_grade
      courseNumber: course_id
    }
  }
  advisorId:advisor_id
  dormitory: student_dormitory @insert @update @object
  {
    dormId: dorm_id
    dormName: dorm_name
    ora$student_flex @flex
  }
  studentId @generated (path: "$._id")
  ora$student_flex @flex
}';

EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
  _id : course_id
  name
  Notes: notes
  teacher: teacher_root @insert @update @object
  {
    name
    teacherId: "_id"
    ora$course_flex @flex
  }
  courseId @generated (path: "$._id")
  students: map_course_root_to_student_root @insert @update @delete @array
  {
    ora$mapCourseId: map_course_id
    ora$mapStudentId: map_student_id
    ora$course_flex @flex
    student_root @unnest @insert @update @object
    {
      name
      studentId: student_id
    }
  }
  creditHours: credit_hours
  ora$course_flex @flex
}';

EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
  "_id"
  name
  salary
  department
  phoneNumber: phone_number
  coursesTaught: course_root @insert @update @delete @array
  {
    name
    courseId: course_id
    classType: class_type
    ora$teacher_flex @flex
  }
  studentsAdvised: student_root @insert @update @delete @array
  {
    name
    dormId:dorm_id
    studentId: student_id
    ora$teacher_flex @flex
  }
  ora$teacher_flex @flex
}';

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_STUDENT
  BEFORE INSERT
  ON STUDENT
  FOR EACH ROW
DECLARE
  inp_jsonobj json_object_t;
BEGIN
  inp_jsonobj := json_object_t(:new.data);
  IF NOT inp_jsonobj.has(''_id'')
  THEN
    inp_jsonobj.put(''_id'', inp_jsonobj.get(''studentId''));
    :new.data := inp_jsonobj.to_json;
  END IF;
END;';

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_COURSE
  BEFORE INSERT
  ON COURSE
  FOR EACH ROW
DECLARE
  inp_jsonobj json_object_t;
BEGIN
  inp_jsonobj := json_object_t(:new.data);
  IF NOT inp_jsonobj.has(''_id'')
  THEN
    inp_jsonobj.put(''_id'', inp_jsonobj.get(''courseId''));
    :new.data := inp_jsonobj.to_json;
  END IF;
END;';
END;

After executing the DDL code the conversion is complete, but we need to validate it, using PL/SQL function DBMS_JSON_DUALITY.validate_schema_report. That shows no errors (no rows selected) for each duality view, which means there are no validation failures — the duality views and the relational schema they represent are good.

Example 21-13 VALIDATE_SCHEMA_REPORT for No Outlier Use Case

SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
                                  table_name => 'TEACHER_TAB',
                                  view_name  => 'TEACHER');
no rows selected
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
                                  table_name => 'COURSE_TAB',
                                  view_name  => 'COURSE');
no rows selected
SELECT * FROM DBMS_JSON_DUALITY.validate_schema_report(
                                  table_name => 'STUDENT_TAB',
                                  view_name  => 'STUDENT');
no rows selected

The duality views are still empty, not yet populated with the input data. We next (1) create error logs for the views and then (2) import the data from the temporary transfer tables, *_TAB into the views, using procedure import_all.

Example 21-14 Creating Error Logs for No Outlier Use Case

BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name     => 'COURSE',
                             err_log_table_name => 'COURSE_ERR_LOG',
                             skip_unsupported   => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name     => 'TEACHER',
                             err_log_table_name => 'TEACHER_ERR_LOG',
                             skip_unsupported   => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name     => 'STUDENT',
                             err_log_table_name => 'STUDENT_ERR_LOG',
                             skip_unsupported   => TRUE);
END;
/

Error logging only reports documents that can't be imported (and only the first such error encountered in a given document is reported).

Example 21-15 Importing Document Sets, for No Outlier Use Case

BEGIN
DBMS_JSON_DUALITY.import_all(
                   JSON('{"tableNames" : [ "STUDENT_TAB",
                                           "TEACHER_TAB",
                                           "COURSE_TAB" ],
                          "viewNames"  : [ "STUDENT",
                                           "TEACHER",
                                           "COURSE" ],
                          "errorLog"   : [ "STUDENT_ERR_LOG",
                                           "TEACHER_ERR_LOG",
                                           "COURSE_ERR_LOG" ]}'));
END;
/

Import done; the duality views are populated.

Example 21-16 Checking Error Logs from Import, for No Outlier Use Case

The error logs are empty, showing that there are no import errors — there are no documents that didn't get imported. 

SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
no rows selected
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
no rows selected
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
no rows selected

We next use DBMS_JSON_DUALITY.validate_import_report to report on any problems with documents that have been imported successfully. In this case, nothing is reported (no rows selected), which means that there are no such problems.

Example 21-17 VALIDATE_IMPORT_REPORT for No Outlier Use Case

SELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
                                  table_name => 'TEACHER_TAB',
                                  view_name  => 'TEACHER');
no rows selected
SELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
                                  table_name => 'TEACHER_TAB',
                                  view_name  => 'TEACHER');
no rows selected
SELECT * FROM DBMS_JSON_DUALITY.validate_import_report(
                                  table_name => 'TEACHER_TAB',
                                  view_name => 'TEACHER');
no rows selected

Note:

An example of a problem that could be reported by validate_import_report is a contradiction between documents that were successfully imported. For example, if a course document says that the teacher of the MATH101 course (Algebra) is Natalie C. and a teacher document says that the teacher of that course is Abdul J., those documents are incompatible. The import validation report would provide a JSON Patch recipe that reconciles the problem by altering documents, for example by removing MATH101 from the teacher document for Abdul J., and adding it to the teacher document for Natalie C. That particular reconciliation might or might not be the one you want; a better data correction might be to instead change the course document for MATH101 to show Abdul J. as the teacher. Only you know which content corrections are the most appropriate.

You can pretty-print the document collections supported by the resulting duality views using SQL function json_serialize, like this: 

SELECT json_serialize(data PRETTY) FROM student;
Comparing the documents supported by the duality views with the original documents in School Administration Example, Migrator Input Documents or in the temporary transfer tables shows that the data is the same, except for the addition of the following:

These differences are expected. See JSON-To-Duality Converter: What It Does.

Here's a document supported by the student duality view:

{"_id" : 1,
 "_metadata" : {"etag" : "39BA872C7E20186761BDD47B8AF40E3D",
                "asof" : "000000000043EF3F"},
 "age" : 20,
 "name" : "Donald P.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 1,
                  "name"             : "Algorithms",
                  "avgGrade"         : 75,
                  "courseNumber"     : "CS101"},
                 {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 1,
                  "name"             : "Data Structures",
                  "avgGrade"         : "TBD",
                  "courseNumber"     : "CS102"},
                 {"ora$mapCourseId"  : "MATH101",
                  "ora$mapStudentId" : 1,
                  "name"             : "Algebra",
                  "avgGrade"         : 90,
                  "courseNumber"     : "MATH101"} ],
 "advisorId" : 102,
 "dormitory" : {"dormId" : 201, "dormName" : "ABC"},
 "studentId" : 1}
SELECT json_serialize(data PRETTY) FROM student;
JSON_SERIALIZE(DATAPRETTY)
--------------------------
{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "39BA872C7E20186761BDD47B8AF40E3D",
    "asof" : "0000000000461E3D"
  },
  "age" : 20,
  "name" : "Donald P.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 1,
      "name" : "Algorithms",
      "avgGrade" : 75,
      "courseNumber" : "CS101"
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 1,
      "name" : "Data Structures",
      "avgGrade" : "TBD",
      "courseNumber" : "CS102"
    },
    {
      "ora$mapCourseId" : "MATH101",
      "ora$mapStudentId" : 1,
      "name" : "Algebra",
      "avgGrade" : 90,
      "courseNumber" : "MATH101"
    }
  ],
  "advisorId" : 102,
  "dormitory" :
  {
    "dormId" : 201,
    "dormName" : "ABC"
  },
  "studentId" : 1
}

{
  "_id" : 2,
  "_metadata" :
  {
    "etag" : "65B5DD1BE7B819306F2735F325E26400",
    "asof" : "0000000000461E3D"
  },
  "age" : 21,
  "name" : "Elena H.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 2,
      "name" : "Algorithms",
      "avgGrade" : 75,
      "courseNumber" : "CS101"
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 2,
      "name" : "Data Structures",
      "avgGrade" : "TBD",
      "courseNumber" : "CS102"
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 2,
      "name" : "Calculus",
      "avgGrade" : 95,
      "courseNumber" : "MATH102"
    }
  ],
  "advisorId" : 103,
  "dormitory" :
  {
    "dormId" : 202,
    "dormName" : "XYZ"
  },
  "studentId" : 2
}

{
  "_id" : 3,
  "_metadata" :
  {
    "etag" : "E5AE58B21076D06FBA05010F0E1BEF21",
    "asof" : "0000000000461E3D"
  },
  "age" : 20,
  "name" : "Francis K.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 3,
      "name" : "Advanced Algebra",
      "avgGrade" : 82,
      "courseNumber" : "MATH103"
    }
  ],
  "advisorId" : 103,
  "dormitory" :
  {
    "dormId" : 204,
    "dormName" : "QWE"
  },
  "studentId" : 3
}

{
  "_id" : 4,
  "_metadata" :
  {
    "etag" : "D3B57FC478449FA24E123432C9D38673",
    "asof" : "0000000000461E3D"
  },
  "age" : 19,
  "name" : "Georgia D.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 4,
      "name" : "Algorithms",
      "avgGrade" : 75,
      "courseNumber" : "CS101"
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 4,
      "name" : "Calculus",
      "avgGrade" : 95,
      "courseNumber" : "MATH102"
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 4,
      "name" : "Advanced Algebra",
      "avgGrade" : 82,
      "courseNumber" : "MATH103"
    }
  ],
  "advisorId" : 101,
  "dormitory" :
  {
    "dormId" : 203,
    "dormName" : "LMN"
  },
  "studentId" : 4
}

{
  "_id" : 5,
  "_metadata" :
  {
    "etag" : "3FA71878EA5F02343CD62BC97F4C078E",
    "asof" : "0000000000461E3D"
  },
  "age" : 21,
  "name" : "Hye E.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 5,
      "name" : "Data Structures",
      "avgGrade" : "TBD",
      "courseNumber" : "CS102"
    },
    {
      "ora$mapCourseId" : "MATH101",
      "ora$mapStudentId" : 5,
      "name" : "Algebra",
      "avgGrade" : 90,
      "courseNumber" : "MATH101"
    }
  ],
  "advisorId" : 103,
  "dormitory" :
  {
    "dormId" : 201,
    "dormName" : "ABC"
  },
  "studentId" : 5
}

{
  "_id" : 6,
  "_metadata" :
  {
    "etag" : "6F06B3DFCAEB4CF71669FDA9263B3236",
    "asof" : "0000000000461E3D"
  },
  "age" : 21,
  "name" : "Ileana D.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 6,
      "name" : "Advanced Algebra",
      "avgGrade" : 82,
      "courseNumber" : "MATH103"
    }
  ],
  "advisorId" : 102,
  "dormitory" :
  {
    "dormId" : 205,
    "dormName" : "GHI"
  },
  "studentId" : 6
}

{
  "_id" : 7,
  "_metadata" :
  {
    "etag" : "6A44A0B63DEC99978D98813B9D7C1D07",
    "asof" : "0000000000461E3D"
  },
  "age" : 20,
  "name" : "Jatin S.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 7,
      "name" : "Algorithms",
      "avgGrade" : 75,
      "courseNumber" : "CS101"
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 7,
      "name" : "Data Structures",
      "avgGrade" : "TBD",
      "courseNumber" : "CS102"
    }
  ],
  "advisorId" : 101,
  "dormitory" :
  {
    "dormId" : 204,
    "dormName" : "QWE"
  },
  "studentId" : 7
}

{
  "_id" : 8,
  "_metadata" :
  {
    "etag" : "0B254C00DBCAA2E59DE30377138BD004",
    "asof" : "0000000000461E3D"
  },
  "age" : 21,
  "name" : "Katie H.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 8,
      "name" : "Data Structures",
      "avgGrade" : "TBD",
      "courseNumber" : "CS102"
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 8,
      "name" : "Advanced Algebra",
      "avgGrade" : 82,
      "courseNumber" : "MATH103"
    }
  ],
  "advisorId" : 102,
  "dormitory" :
  {
    "dormId" : 205,
    "dormName" : "GHI"
  },
  "studentId" : 8
}

{
  "_id" : 9,
  "_metadata" :
  {
    "etag" : "32D58F0278F226E26A5D4039A01D1288",
    "asof" : "0000000000461E3D"
  },
  "age" : "Nineteen",
  "name" : "Luis F.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 9,
      "name" : "Algorithms",
      "avgGrade" : 75,
      "courseNumber" : "CS101"
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 9,
      "name" : "Calculus",
      "avgGrade" : 95,
      "courseNumber" : "MATH102"
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 9,
      "name" : "Advanced Algebra",
      "avgGrade" : 82,
      "courseNumber" : "MATH103"
    }
  ],
  "advisorId" : 101,
  "dormitory" :
  {
    "dormId" : 201,
    "dormName" : "ABC"
  },
  "studentId" : 9
}

{
  "_id" : 10,
  "_metadata" :
  {
    "etag" : "979816C4FD15DC805007B9FF7D822168",
    "asof" : "0000000000461E3D"
  },
  "age" : 20,
  "name" : "Ming L.",
  "courses" :
  [
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 10,
      "name" : "Calculus",
      "avgGrade" : 95,
      "courseNumber" : "MATH102"
    }
  ],
  "advisorId" : 101,
  "dormitory" :
  {
    "dormId" : 202,
    "dormName" : "XYZ"
  },
  "studentId" : 10
}

10 rows selected.

You can also create data-guide JSON schemas that describe the document sets supported by the duality views, for comparison with those for the input document sets. Creating them is identical to creating the data guides for the input tables (see "Create JSON Data Guides for Input Document Sets" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas), except that the input data is selected from duality views student, teacher, and course, instead of from input transfer tables student_tab, teacher_tab, and course_tab.

The schemas are identical for each kind of documents (input and view-supported), except for the following:

  • Document-identifier and document-handling fields _id and _metadata are added to the duality-view schemas.

  • Fields such as ora$mapCourseId and ora$mapStudentId are added to the duality-view schemas. These identify columns of the mapping table.

  • Dates in field o:last_analyzed differ. These just record when the data guide was created.

Except for field o:last_analyzed, these are the same differences noted above as existing between the (1) the original input documents and the transfer-table documents, on the one hand, and (2) the documents supported by the duality views, on the other hand. This points to a general tip:

Tip:

You can compare JSON schemas that model document sets as a shortcut for comparing the document sets. Of course, JSON schemas don't contain all of the information in the documents they describe, but they can highlight structure and typing differences, and thus serve as a proxy that gives you a good 50,000-foot view.

See Also:

Parent topic: Migrating From JSON To Duality


Footnote Legend

Footnote 1: Primary-key column values cannot be NULL.
Footnote 2: JSON array indexing is zero-based.