21.11 Import After Default Conversion

After default conversion (except for minFieldFrequency and minTypeFrequency), in particular with useFlexFields:true), almost all documents from the student, teacher, and course input document sets are successfully imported, but some fields are not exactly as they were in the original, input documents.

The process of creating error logs and importing the input document sets (in tables student_tab, teacher_tab, and course_tab) into the duality views created in Using the Converter, Default Behavior is exactly the same as in the simplified recipe case: see "Creating Error Logs for No Outlier Use Case" and "Importing Document Sets, for No Outlier Use Case" in Migrating To Duality, Simplified Recipe. Checking the error logs for the default case tells a different story.

Example 21-23 Checking Error Logs from Import, for Default Case

There are no errors logged for import into duality views teacher and course. But unlike the simplified recipe case ("Checking Error Logs from Import, for No Outlier Use Case" in Migrating To Duality, Simplified Recipe), import into the student view logs an error for the type-occurrence outlier for field age with value "Nineteen".

SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$
  FROM student_err_log;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
-------------
ORA_ERR_TAG$
------------
42555

ORA-42555: Cannot insert into JSON Relational Duality View 'STUDENT': The input JSON document is invalid.

JZN-00671: value of field 'age' can not be converted to a number

Import Error

Select the culprit student document from the input student table:

SELECT * FROM "JANUS".student_tab
  WHERE ROWID IN (SELECT ora_err_rowid$ FROM student_err_log);

DATA
----
{"studentId":9,"name":"Luis F.","age":"Nineteen","advisorId":101,"courses":[{"co
urseNumber":"CS101","name":"Algorithms","avgGrade":75},{"courseNumber":"MATH102"
,"name":"Calculus","avgGrade":95},{"courseNumber":"MATH103","name":"Advanced Alg
ebra","avgGrade":82}],"dormitory":{"dormId":201,"dormName":"ABC"}}

Unlike what happens in the simplified migration case (see "VALIDATE_IMPORT_REPORT for No Outlier Use Case" in Migrating To Duality, Simplified Recipe), validating the import using DBMS_JSON_DUALITY.validate_import_report reports an error for the documents that have been imported successfully: the student document for Luis F. has a null value for its age field, corresponding to the input string value "Nineteen".

PL/SQL function validate_import_report compares input documents with documents imported into the duality views, ignoring any additional fields added by the converter (_id, _metadata, ora$map*, ora$*_flex). It uses the format of JSON Patch to identify the problematic fields and specify editing operations you can perform on the input data to resolve the problems (differences).

The examples that follow ("Student Duality View Document Collection (useFlexFields = true)", "Teacher Duality View Document Collection (useFlexFields = true)" and "Course Duality View Document Collection (useFlexFields = true)") show the document collections supported by the duality views, that is, the result of importing.

Example 21-24 Student Duality View Document Collection (useFlexFields = true)

Compare this with the input student document set, School Administration Example, Migrator Input Documents, which (with conversion using minFieldFrequency = 25 and minTypeFrequency = 15) has only one outlier field: age (with a type-occurrence frequency of 10%).

These are the only differences (ignoring field order, which is irrelevant):

• Document identifier field _id and document-state field _metadata have been added. (Every document supported by a duality view has these fields.)

• Fields ora$mapCourseId and ora$mapStudentId have been added. These correspond to the identifying columns (primary-key columns in this case) for underlying mapping table map_table_course_root_to_student_root. Their values are the same as the values of fields courseNumber and studentId, respectively.

• Even though the document for student Luis F. (studentId = 9) failed import into the student duality view (because field age has the string value "Nineteen", and its 10% occurrence is a type-occurrence outlier), that document is nevertheless present in the duality view. When we import documents into the course and teacher duality views, a row is added to table student_root that has 9 as the value for column student_root.student_id, because studentId with value 9 is present in both input tables course_tab and teacher_tab.

  The age field value for that student document for Luis F. is null, however (not "Nineteen" and not 19). No age field exists in either of the course or teacher input document sets, so importing their student data for Luis F. into the course and teacher views stores NULL in the age column in table student_root. And that NULL column value maps to JSON null in the student documents.

There are no other differences. In particular, mixed-type field avgGrade is unchanged from the input data, as it is not an outlier: each of its types occurs in more than 15% of the documents.

{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "4F39C8B86F4295AD2958B18A77B0AACC",
    "asof" : "0000000000423804"
  },
  "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" : "758A4F3E6EF3152A4FA0892AB38635D4",
    "asof" : "0000000000423804"
  },
  "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" : "06905F120EF74124C5985354BBCE5CC1",
    "asof" : "0000000000423804"
  },
  "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" : "50847D1AB63537118A6133A4CC1B8708",
    "asof" : "0000000000423804"
  },
  "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" : "FD6E27A868C56D1EF9C7AEB3F08C7F9B",
    "asof" : "0000000000423804"
  },
  "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" : "2BDA7862330B0687F22F830F3E314E34",
    "asof" : "0000000000423804"
  },
  "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" : "F1EF0CCD54EDFA78D2263D7E742D6CE8",
    "asof" : "0000000000423804"
  },
  "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" : "9A25A267BC08858E0F754E0C00B32F9E",
    "asof" : "0000000000423804"
  },
  "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" : 10,
  "_metadata" :
  {
    "etag" : "94376DA05B92E47718AF70A31FBE56E7",
    "asof" : "0000000000423804"
  },
  "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
}

{
  "_id" : 9,
  "_metadata" :
  {
    "etag" : "579824C71904C46901BBA605E8539943",
    "asof" : "0000000000423804"
  },
  "age" : null,
  "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
}

Example 21-25 Teacher Duality View Document Collection (useFlexFields = true)

Compare this with the input teacher document set, in School Administration Example, Migrator Input Documents, which had no outliers.

The only difference (ignoring field order, which is irrelevant) is that document identifier field _id and document-state field _metadata have been added. (Every document supported by a duality view has these fields.)

Field phoneNumber is of mixed type: 50% string and 50% array of strings. (Because it's of mixed type and is not a type-occurrence outlier, it's stored in its own JSON-type column.)

{
  "_id" : 101,
  "_metadata" :
  {
    "etag" : "F919587CCFAD69F2208B0CDDC80BFAB8",
    "asof" : "000000000042348C"
  },
  "name" : "Abdul J.",
  "salary" : 200000,
  "department" : "Mathematics",
  "phoneNumber" :
  [
    "222-555-011",
    "222-555-012"
  ],
  "coursesTaught" :
  [
    {
      "name" : "Algebra",
      "courseId" : "MATH101",
      "classType" : "Online"
    },
    {
      "name" : "Calculus",
      "courseId" : "MATH102",
      "classType" : "In-person"
    }
  ],
  "studentsAdvised" :
  [
    {
      "name" : "Georgia D.",
      "dormId" : 203,
      "studentId" : 4
    },
    {
      "name" : "Jatin S.",
      "dormId" : 204,
      "studentId" : 7
    },
    {
      "name" : "Luis F.",
      "dormId" : 201,
      "studentId" : 9
    },
    {
      "name" : "Ming L.",
      "dormId" : 202,
      "studentId" : 10
    }
  ]
}

{
  "_id" : 102,
  "_metadata" :
  {
    "etag" : "657E2A688F0A086D948A557ABB1FE3BC",
    "asof" : "000000000042348C"
  },
  "name" : "Betty Z.",
  "salary" : 300000,
  "department" : "Computer Science",
  "phoneNumber" : "222-555-022",
  "coursesTaught" :
  [
    {
      "name" : "Algorithms",
      "courseId" : "CS101",
      "classType" : "Online"
    },
    {
      "name" : "Data Structures",
      "courseId" : "CS102",
      "classType" : "In-person"
    }
  ],
  "studentsAdvised" :
  [
    {
      "name" : "Donald P.",
      "dormId" : 201,
      "studentId" : 1
    },
    {
      "name" : "Ileana D.",
      "dormId" : 205,
      "studentId" : 6
    },
    {
      "name" : "Katie H.",
      "dormId" : 205,
      "studentId" : 8
    }
  ]
}

{
  "_id" : 103,
  "_metadata" :
  {
    "etag" : "1F2DB9CBCD6F7E5E558785D78CA7D116",
    "asof" : "000000000042348C"
  },
  "name" : "Colin J.",
  "salary" : 220000,
  "department" : "Mathematics",
  "phoneNumber" :
  [
    "222-555-023"
  ],
  "coursesTaught" :
  [
    {
      "name" : "Advanced Algebra",
      "courseId" : "MATH103",
      "classType" : "Online"
    }
  ],
  "studentsAdvised" :
  [
    {
      "name" : "Elena H.",
      "dormId" : 202,
      "studentId" : 2
    },
    {
      "name" : "Francis K.",
      "dormId" : 204,
      "studentId" : 3
    },
    {
      "name" : "Hye E.",
      "dormId" : 201,
      "studentId" : 5
    }
  ]
}

{
  "_id" : 104,
  "_metadata" :
  {
    "etag" : "D4D644FB68590D5A00EC53778F0E7226",
    "asof" : "000000000042348C"
  },
  "name" : "Natalie C.",
  "salary" : 180000,
  "department" : "Computer Science",
  "phoneNumber" : "222-555-044",
  "coursesTaught" :
  [
  ],
  "studentsAdvised" :
  [
  ]
}

Example 21-26 Course Duality View Document Collection (useFlexFields = true)

Compare this with the input course document set, in School Administration Example, Migrator Input Documents, which (with conversion using minFieldFrequency = 25 and minTypeFrequency = 15) has only one outlier field: Notes (with a field occurrence frequency of 20%).

Field Notes is nevertheless present in the duality-view document for course MATH101, because conversion was done with useFlexFields = true, which means the converter created flex columns in the duality views — the importer stored field Notes in a flex column.

The only other difference from the input documents (ignoring field order, which is irrelevant) is that document identifier field _id and document-state field _metadata have been added. Every document supported by a duality view has these fields.

Note that there's no difference for field creditHours. It's of mixed type, number-or-string (that is, the value can be a number or a string). And even though only one document (for course MATH103) uses a string value, the field is not a type-occurrence outlier because a string occurs in one of the five documents (20%), which is greater than minTypeFrequency = 15.

Note too that field _id has a string value, such as "MATH101", because it is mapped to input field courseId. A document-identifier field need not be a number; its value just needs to uniquely identify a document.

{
  "_id" : "CS101",
  "_metadata" :
  {
    "etag" : "FE5B789404D0B9945EB69D7036759CF2",
    "asof" : "0000000000423494"
  },
  "name" : "Algorithms",
  "teacher" :
  {
    "name" : "Betty Z.",
    "teacherId" : 102
  },
  "students" :
  [
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 1,
      "name" : "Donald P.",
      "studentId" : 1
    },
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 2,
      "name" : "Elena H.",
      "studentId" : 2
    },
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 4,
      "name" : "Georgia D.",
      "studentId" : 4
    },
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 7,
      "name" : "Jatin S.",
      "studentId" : 7
    },
    {
      "ora$mapCourseId" : "CS101",
      "ora$mapStudentId" : 9,
      "name" : "Luis F.",
      "studentId" : 9
    }
  ],
  "creditHours" : 5,
  "courseId" : "CS101"
}

{
  "_id" : "CS102",
  "_metadata" :
  {
    "etag" : "D2A2D30D1998AAABC4D6EC5FDAFB2472",
    "asof" : "0000000000423494"
  },
  "name" : "Data Structures",
  "teacher" :
  {
    "name" : "Betty Z.",
    "teacherId" : 102
  },
  "students" :
  [
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 1,
      "name" : "Donald P.",
      "studentId" : 1
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 2,
      "name" : "Elena H.",
      "studentId" : 2
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 5,
      "name" : "Hye E.",
      "studentId" : 5
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 7,
      "name" : "Jatin S.",
      "studentId" : 7
    },
    {
      "ora$mapCourseId" : "CS102",
      "ora$mapStudentId" : 8,
      "name" : "Katie H.",
      "studentId" : 8
    }
  ],
  "creditHours" : 3,
  "courseId" : "CS102"
}

{
  "_id" : "MATH101",
  "_metadata" :
  {
    "etag" : "3509714A03884A40BC1EBE0952E3F5CE",
    "asof" : "0000000000423494"
  },
  "name" : "Algebra",
  "teacher" :
  {
    "name" : "Abdul J.",
    "teacherId" : 101
  },
  "students" :
  [
    {
      "ora$mapCourseId" : "MATH101",
      "ora$mapStudentId" : 1,
      "name" : "Donald P.",
      "studentId" : 1
    },
    {
      "ora$mapCourseId" : "MATH101",
      "ora$mapStudentId" : 5,
      "name" : "Hye E.",
      "studentId" : 5
    }
  ],
  "creditHours" : 3,
  "Notes" : "Prerequisite for Advanced Algebra",
  "courseId" : "MATH101"
}

{
  "_id" : "MATH102",
  "_metadata" :
  {
    "etag" : "3193D7B3FC1EC95210D4ABF12DF1558E",
    "asof" : "0000000000423494"
  },
  "name" : "Calculus",
  "teacher" :
  {
    "name" : "Abdul J.",
    "teacherId" : 101
  },
  "students" :
  [
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 2,
      "name" : "Elena H.",
      "studentId" : 2
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 4,
      "name" : "Georgia D.",
      "studentId" : 4
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 9,
      "name" : "Luis F.",
      "studentId" : 9
    },
    {
      "ora$mapCourseId" : "MATH102",
      "ora$mapStudentId" : 10,
      "name" : "Ming L.",
      "studentId" : 10
    }
  ],
  "creditHours" : 4,
  "courseId" : "MATH102"
}

{
  "_id" : "MATH103",
  "_metadata" :
  {
    "etag" : "8AC5912C1CB56D431FF4F979EB121E60",
    "asof" : "0000000000423494"
  },
  "name" : "Advanced Algebra",
  "teacher" :
  {
    "name" : "Colin J.",
    "teacherId" : 103
  },
  "students" :
  [
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 3,
      "name" : "Francis K.",
      "studentId" : 3
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 4,
      "name" : "Georgia D.",
      "studentId" : 4
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 6,
      "name" : "Ileana D.",
      "studentId" : 6
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 8,
      "name" : "Katie H.",
      "studentId" : 8
    },
    {
      "ora$mapCourseId" : "MATH103",
      "ora$mapStudentId" : 9,
      "name" : "Luis F.",
      "studentId" : 9
    }
  ],
  "creditHours" : "3",
  "courseId" : "MATH103"
}

The example that follows, "Create JSON Data Guides for Document Collections Supported By Duality Views" , creates data-guide JSON schemas for each of the duality views, that is, for the document sets supported by the views. You can compare the schema for each duality view with a data-guide JSON schema that describes the corresponding input document set (see the example "Create JSON Data Guides for Input Document Sets" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas).

A data-guide schema serves as a shortcut (proxy) for comparing the documents supported by a duality view with the corresponding input documents. Such comparison can help decide how you might want to (1) change some of the documents, (2) change some of the configuration fields used to infer and generate the database objects, or (3) change the definition of a duality view or table.

It's important to note that comparing JSON schemas between input and output database objects (input transfer table and output duality view) is not the same as comparing the input and output documents. Comparing schemas can suggest things you might want to change, but it isn't a complete substitute for comparing documents. After you import the original documents into the duality views you can and should compare documents.

Example 21-27 Create JSON Data Guides For Document Collections Supported By Duality Views

This code is identical to the data-guide JSON schema creation code in the example "Create JSON Data Guides for Input Document Sets" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas, except that the data guides here are created on duality views, not input tables.

SELECT json_dataguide(data,
                      DBMS_JSON.FORMAT_SCHEMA,
                      DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS)
  FROM student;

SELECT json_dataguide(data,
                      DBMS_JSON.FORMAT_SCHEMA,
                      DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS)
  FROM teacher;

SELECT json_dataguide(data,
                      DBMS_JSON.FORMAT_SCHEMA,
                      DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS)
  FROM course;

Example 21-28 Student Duality View Data Guide

This data guide JSON schema summarizes the collection of student documents supported by duality view student.

The differences from the data guide for the student input documents, in the example "Create JSON Data Guides for Input Student Document Set" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas) reflect the differences between the two sets of student documents (see "Student Duality View Document Collection (useFlexFields = true)" in Import After Default Conversion).

{
  "type" : "object",
  "o:frequency" : 100,
  "o:last_analyzed" : "2025-01-10T17:29:04",
  "o:sample_size" : 10,
  "required" : true,
  "properties" :
  {
    "_id" :
    {
      "type" : "number",
      "o:preferred_column_name" : "_id",
      "o:frequency" : 100,
      "o:low_value" : 1,
      "o:high_value" : 10,
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "maximum" : 10,
      "minimum" : 1
    },
    "age" :
    {
      "oneOf" :
      [
        {
          "type" : "null",
          "o:preferred_column_name" : "age",
          "o:frequency" : 10,
          "o:low_value" : null,
          "o:high_value" : null,
          "o:num_nulls" : 1,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10
        },
        {
          "type" : "number",
          "o:preferred_column_name" : "age",
          "o:frequency" : 90,
          "o:low_value" : 19,
          "o:high_value" : 21,
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10,
          "maximum" : 21,
          "minimum" : 19
        }
      ]
    },
    "name" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "name",
      "o:frequency" : 100,
      "o:low_value" : "Donald P.",
      "o:high_value" : "Ming L.",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "maxLength" : 10,
      "minLength" : 6
    },
    "courses" :
    {
      "type" : "array",
      "o:preferred_column_name" : "courses",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "items" :
      {
        "properties" :
        {
          "name" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "name",
            "o:frequency" : 100,
            "o:low_value" : "Advanced Algebra",
            "o:high_value" : "Data Structures",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 10,
            "required" : true,
            "maxLength" : 16,
            "minLength" : 7
          },
          "avgGrade" :
          {
            "oneOf" :
            [
              {
                "type" : "number",
                "o:preferred_column_name" : "avgGrade",
                "o:frequency" : 100,
                "o:low_value" : 75,
                "o:high_value" : 95,
                "o:num_nulls" : 0,
                "o:last_analyzed" : "2025-01-10T17:29:04",
                "o:sample_size" : 10,
                "required" : true,
                "maximum" : 95,
                "minimum" : 75
              },
              {
                "type" : "string",
                "o:length" : 4,
                "o:preferred_column_name" : "avgGrade",
                "o:frequency" : 50,
                "o:low_value" : "TBD",
                "o:high_value" : "TBD",
                "o:num_nulls" : 0,
                "o:last_analyzed" : "2025-01-10T17:29:04",
                "o:sample_size" : 10,
                "maxLength" : 3,
                "minLength" : 3
              }
            ]
          },
          "courseNumber" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "courseNumber",
            "o:frequency" : 100,
            "o:low_value" : "CS101",
            "o:high_value" : "MATH103",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 10,
            "required" : true,
            "maxLength" : 7,
            "minLength" : 5
          },
          "ora$mapCourseId" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "ora$mapCourseId",
            "o:frequency" : 100,
            "o:low_value" : "CS101",
            "o:high_value" : "MATH103",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 10,
            "required" : true,
            "maxLength" : 7,
            "minLength" : 5
          },
          "ora$mapStudentId" :
          {
            "type" : "number",
            "o:preferred_column_name" : "ora$mapStudentId",
            "o:frequency" : 100,
            "o:low_value" : 1,
            "o:high_value" : 10,
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 10,
            "required" : true,
            "maximum" : 10,
            "minimum" : 1
          }
        }
      }
    },
    "_metadata" :
    {
      "type" : "object",
      "o:preferred_column_name" : "_metadata",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "properties" :
      {
        "asof" :
        {
          "type" : "binary",
          "o:length" : 8,
          "o:preferred_column_name" : "asof",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10,
          "required" : true
        },
        "etag" :
        {
          "type" : "binary",
          "o:length" : 16,
          "o:preferred_column_name" : "etag",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10,
          "required" : true
        }
      }
    },
    "advisorId" :
    {
      "type" : "number",
      "o:preferred_column_name" : "advisorId",
      "o:frequency" : 100,
      "o:low_value" : 101,
      "o:high_value" : 103,
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "maximum" : 103,
      "minimum" : 101
    },
    "dormitory" :
    {
      "type" : "object",
      "o:preferred_column_name" : "dormitory",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "properties" :
      {
        "dormId" :
        {
          "type" : "number",
          "o:preferred_column_name" : "dormId",
          "o:frequency" : 100,
          "o:low_value" : 201,
          "o:high_value" : 205,
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10,
          "required" : true,
          "maximum" : 205,
          "minimum" : 201
        },
        "dormName" :
        {
          "type" : "string",
          "o:length" : 4,
          "o:preferred_column_name" : "dormName",
          "o:frequency" : 100,
          "o:low_value" : "ABC",
          "o:high_value" : "XYZ",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 10,
          "required" : true,
          "maxLength" : 3,
          "minLength" : 3
        }
      }
    },
    "studentId" :
    {
      "type" : "number",
      "o:preferred_column_name" : "studentId",
      "o:frequency" : 100,
      "o:low_value" : 1,
      "o:high_value" : 10,
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 10,
      "required" : true,
      "maximum" : 10,
      "minimum" : 1
    }
  }
}

Example 21-29 Teacher Duality View Data Guide

This data guide JSON schema summarizes the collection of teacher documents supported by duality view teacher.

The differences from the data guide for the teacher input documents in the example "JSON Data Guide for Input Teacher Document Set" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas reflect the differences between the two sets of teacher documents (see "Teacher Duality View Document Collection (useFlexFields = true)" in Import After Default Conversion).

{
  "type" : "object",
  "o:frequency" : 100,
  "o:last_analyzed" : "2025-01-10T17:29:04",
  "o:sample_size" : 4,
  "required" : true,
  "properties" :
  {
    "_id" :
    {
      "type" : "number",
      "o:preferred_column_name" : "_id",
      "o:frequency" : 100,
      "o:low_value" : 101,
      "o:high_value" : 104,
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "maximum" : 104,
      "minimum" : 101
    },
    "name" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "name",
      "o:frequency" : 100,
      "o:low_value" : "Abdul J.",
      "o:high_value" : "Natalie C.",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "maxLength" : 10,
      "minLength" : 8
    },
    "salary" :
    {
      "type" : "number",
      "o:preferred_column_name" : "salary",
      "o:frequency" : 100,
      "o:low_value" : 180000,
      "o:high_value" : 300000,
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "maximum" : 300000,
      "minimum" : 180000
    },
    "_metadata" :
    {
      "type" : "object",
      "o:preferred_column_name" : "_metadata",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "properties" :
      {
        "asof" :
        {
          "type" : "binary",
          "o:length" : 8,
          "o:preferred_column_name" : "asof",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 4,
          "required" : true
        },
        "etag" :
        {
          "type" : "binary",
          "o:length" : 16,
          "o:preferred_column_name" : "etag",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 4,
          "required" : true
        }
      }
    },
    "department" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "department",
      "o:frequency" : 100,
      "o:low_value" : "Computer Science",
      "o:high_value" : "Mathematics",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "maxLength" : 16,
      "minLength" : 11
    },
    "phoneNumber" :
    {
      "oneOf" :
      [
        {
          "type" : "string",
          "o:length" : 16,
          "o:preferred_column_name" : "phoneNumber",
          "o:frequency" : 50,
          "o:low_value" : "222-555-022",
          "o:high_value" : "222-555-044",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 4,
          "maxLength" : 11,
          "minLength" : 11
        },
        {
          "type" : "array",
          "o:preferred_column_name" : "phoneNumber",
          "o:frequency" : 50,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 4,
          "items" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "scalar_string",
            "o:frequency" : 50,
            "o:low_value" : "222-555-011",
            "o:high_value" : "222-555-023",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maxLength" : 11,
            "minLength" : 11
          }
        }
      ]
    },
    "coursesTaught" :
    {
      "type" : "array",
      "o:preferred_column_name" : "coursesTaught",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "items" :
      {
        "properties" :
        {
          "name" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "name",
            "o:frequency" : 75,
            "o:low_value" : "Advanced Algebra",
            "o:high_value" : "Data Structures",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maxLength" : 16,
            "minLength" : 7
          },
          "courseId" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "courseId",
            "o:frequency" : 75,
            "o:low_value" : "CS101",
            "o:high_value" : "MATH103",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maxLength" : 7,
            "minLength" : 5
          },
          "classType" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "classType",
            "o:frequency" : 75,
            "o:low_value" : "In-person",
            "o:high_value" : "Online",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maxLength" : 9,
            "minLength" : 6
          }
        }
      }
    },
    "studentsAdvised" :
    {
      "type" : "array",
      "o:preferred_column_name" : "studentsAdvised",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 4,
      "required" : true,
      "items" :
      {
        "properties" :
        {
          "name" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "name",
            "o:frequency" : 75,
            "o:low_value" : "Donald P.",
            "o:high_value" : "Ming L.",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maxLength" : 10,
            "minLength" : 6
          },
          "dormId" :
          {
            "type" : "number",
            "o:preferred_column_name" : "dormId",
            "o:frequency" : 75,
            "o:low_value" : 201,
            "o:high_value" : 205,
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maximum" : 205,
            "minimum" : 201
          },
          "studentId" :
          {
            "type" : "number",
            "o:preferred_column_name" : "studentId",
            "o:frequency" : 75,
            "o:low_value" : 1,
            "o:high_value" : 10,
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 4,
            "maximum" : 10,
            "minimum" : 1
          }
        }
      }
    }
  }
}

Example 21-30 Course Duality View Data Guide, for Default Case

This data guide JSON schema summarizes the collection of course documents supported by duality view course, for the conversion case where useFlexFields is true.

The differences from the data guide for the course input documents, in the example "JSON Data Guide for INput Course Document Set" in Before Using the Converter (2): Optionally Create Data-Guide JSON Schemas reflect the differences between the two sets of course documents (see "Course Duality View Document Collection (useFlexFields = true)" in Import After Default Conversion ).

{
  "type" : "object",
  "o:frequency" : 100,
  "o:last_analyzed" : "2025-01-10T17:29:04",
  "o:sample_size" : 5,
  "required" : true,
  "properties" :
  {
    "_id" :
    {
      "type" : "string",
      "o:length" : 8,
      "o:preferred_column_name" : "_id",
      "o:frequency" : 100,
      "o:low_value" : "CS101",
      "o:high_value" : "MATH103",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "maxLength" : 7,
      "minLength" : 5
    },
    "name" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "name",
      "o:frequency" : 100,
      "o:low_value" : "Advanced Algebra",
      "o:high_value" : "Data Structures",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "maxLength" : 16,
      "minLength" : 7
    },
    "Notes" :
    {
      "type" : "string",
      "o:length" : 64,
      "o:preferred_column_name" : "Notes",
      "o:frequency" : 20,
      "o:low_value" : "Prerequisite for Advanced Algebra",
      "o:high_value" : "Prerequisite for Advanced Algebra",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "maxLength" : 33,
      "minLength" : 33
    },
    "teacher" :
    {
      "type" : "object",
      "o:preferred_column_name" : "teacher",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "properties" :
      {
        "name" :
        {
          "type" : "string",
          "o:length" : 8,
          "o:preferred_column_name" : "name",
          "o:frequency" : 100,
          "o:low_value" : "Abdul J.",
          "o:high_value" : "Colin J.",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "required" : true,
          "maxLength" : 8,
          "minLength" : 8
        },
        "teacherId" :
        {
          "type" : "number",
          "o:preferred_column_name" : "teacherId",
          "o:frequency" : 100,
          "o:low_value" : 101,
          "o:high_value" : 103,
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "required" : true,
          "maximum" : 103,
          "minimum" : 101
        }
      }
    },
    "courseId" :
    {
      "type" : "string",
      "o:length" : 8,
      "o:preferred_column_name" : "courseId",
      "o:frequency" : 100,
      "o:low_value" : "CS101",
      "o:high_value" : "MATH103",
      "o:num_nulls" : 0,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "maxLength" : 7,
      "minLength" : 5
    },
    "students" :
    {
      "type" : "array",
      "o:preferred_column_name" : "students",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "items" :
      {
        "properties" :
        {
          "name" :
          {
            "type" : "string",
            "o:length" : 16,
            "o:preferred_column_name" : "name",
            "o:frequency" : 100,
            "o:low_value" : "Donald P.",
            "o:high_value" : "Ming L.",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 5,
            "required" : true,
            "maxLength" : 10,
            "minLength" : 6
          },
          "studentId" :
          {
            "type" : "number",
            "o:preferred_column_name" : "studentId",
            "o:frequency" : 100,
            "o:low_value" : 1,
            "o:high_value" : 10,
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 5,
            "required" : true,
            "maximum" : 10,
            "minimum" : 1
          },
          "ora$mapCourseId" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "ora$mapCourseId",
            "o:frequency" : 100,
            "o:low_value" : "CS101",
            "o:high_value" : "MATH103",
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 5,
            "required" : true,
            "maxLength" : 7,
            "minLength" : 5
          },
          "ora$mapStudentId" :
          {
            "type" : "number",
            "o:preferred_column_name" : "ora$mapStudentId",
            "o:frequency" : 100,
            "o:low_value" : 1,
            "o:high_value" : 10,
            "o:num_nulls" : 0,
            "o:last_analyzed" : "2025-01-10T17:29:04",
            "o:sample_size" : 5,
            "required" : true,
            "maximum" : 10,
            "minimum" : 1
          }
        }
      }
    },
    "_metadata" :
    {
      "type" : "object",
      "o:preferred_column_name" : "_metadata",
      "o:frequency" : 100,
      "o:last_analyzed" : "2025-01-10T17:29:04",
      "o:sample_size" : 5,
      "required" : true,
      "properties" :
      {
        "asof" :
        {
          "type" : "binary",
          "o:length" : 8,
          "o:preferred_column_name" : "asof",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "required" : true
        },
        "etag" :
        {
          "type" : "binary",
          "o:length" : 16,
          "o:preferred_column_name" : "etag",
          "o:frequency" : 100,
          "o:low_value" : "",
          "o:high_value" : "",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "required" : true
        }
      }
    },
    "creditHours" :
    {
      "oneOf" :
      [
        {
          "type" : "number",
          "o:preferred_column_name" : "creditHours",
          "o:frequency" : 80,
          "o:low_value" : 3,
          "o:high_value" : 5,
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "maximum" : 5,
          "minimum" : 3
        },
        {
          "type" : "string",
          "o:length" : 1,
          "o:preferred_column_name" : "creditHours",
          "o:frequency" : 20,
          "o:low_value" : "3",
          "o:high_value" : "3",
          "o:num_nulls" : 0,
          "o:last_analyzed" : "2025-01-10T17:29:04",
          "o:sample_size" : 5,
          "maxLength" : 1,
          "minLength" : 1
        }
      ]
    }
  }
}

You can also use DBMS_JSON_SCHEMA.describe to create a JSON schema that shows different information about the duality views.

See Also:

• DBMS_JSON_DUALITY in Oracle Database PL/SQL Packages and Types Reference for information about function validate_import_report

• JSON Patch and JSON Pointer for information about the error content reported by DBMS_JSON_DUALITY.validate_import_report

• JSON Schemas Generated with DBMS_JSON_SCHEMA.DESCRIBE in Oracle Database JSON Developer’s Guide

• DESCRIBE Function in Oracle Database PL/SQL Packages and Types Reference