21.12 Using the Converter with useFlexFields=false

Use of the JSON-to-duality converter with useFlexFields = false is illustrated. Otherwise the configuration is default (except for minFieldFrequency and minTypeFrequency). The database objects needed to support the document sets are inferred, and the SQL DDL code to construct them is generated.

We pass useFlexFields with a false value to DBMS_JSON_DUALITY.infer_schema. Otherwise, that call is the same as in the example "INFER_SCHEMA and GENERATE_SCHEMA with useFlexFields = true" in Using the Converter, Default Behavior. And the JSON schema returned by infer_schema is the same for both true and false useFlexFields.

The call to DBMS_JSON_DUALITY.generate_schema is also the same. The only differences in the generated DDL code are these:

  • The tables underlying the duality views have no flex columns when useFlexFields is false.

  • The duality views don't refer to flex columns when useFlexFields is false.

Example 21-31 DDL Code from GENERATE_SCHEMA with useFlexFields = false

Function DBMS_JSON_DUALITY.generate_schema, produces the generated DDL code shown here if passed the JSON schema produced by function infer_schema with useFlexFields = false.

The only difference in this DDL code from that generated with useFlexFields = true is that here there are no flex columns in the underlying tables and no references to flex columns in the duality views. 

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
   dorm_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   dorm_name  varchar2(64),
   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,
   PRIMARY KEY(map_course_id,map_student_id)
)';

EXECUTE IMMEDIATE 'CREATE TABLE student_root(
   age  number,
   name  varchar2(64),
   dorm_id  number,
   advisor_id  number,
   student_id  number  GENERATED BY DEFAULT ON NULL AS IDENTITY,
   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"}]}'',
   PRIMARY KEY("_id")
)';

EXECUTE IMMEDIATE 'CREATE TABLE course_root(
   name  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,
   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_student_dormitory FOREIGN KEY (dorm_id) REFERENCES student_dormitory(dorm_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 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_student_dormitory_index ON student_root(dorm_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_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
    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
  }
  studentId @generated (path: "$._id")
}';

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
  }
  studentsAdvised: student_root @insert @update @delete @array
  {
    name
    dormId:dorm_id
    studentId: student_id
  }
}';

EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
  _id : course_id
  name
  teacher: teacher_root @insert @update @object
  {
    name
    teacherId: "_id"
  }
  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
    student_root @unnest @insert @update @object
    {
      name
      studentId: student_id
    }
  }
  creditHours: credit_hours
}';

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;

The example that follows, "VALIDATE_SCHEMA_REPORT with useFlexFields = false", shows that rare field Notes is an outlier for the course document set.

With no flex columns created by the converter, errors will be logged during import for any fields that are unmapped by the converter (for example, fields that can't be stored in a simple SQL scalar column). When useFlexFields is true (the default value) unmapped field Notes is retained in course documents, by being stored in a flex field. But with useFlexFields false field Notes is logged during import as an error.

When you generate the DDL code and then execute it, the duality views and their underlying tables are created.

After executing the DDL code, you run converter function DBMS_JSON_DUALITY.validate_schema_report for each kind (student, teacher, course) of input table and duality view, to validate the conversion.

Example 21-32 VALIDATE_SCHEMA_REPORT with useFlexFields = false

This code is the same as that in the example "VALIDATE_SCHEMA_REPORT for Default Case (useFlexFields = true) " in Using the Converter, Default Behavior. 

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

The error reported for the student table and view is the same as in the example "VALIDATE_SCHEMA_REPORT for Default Case (useFlexFields = true) " in Using the Converter, Default Behavior. (That is, the string value of "Nineteen" for field age).

There are no errors reported for the teacher table and view (just as in the useFlexFields = true case).

Instead of no errors reported for the course table and view (as in the useFlexFields = true case), however, an error is reported for field occurrence-outlier field Notes in the useFlexFields = false case. This is because in the default case that field is stored in a flex column. 

DATA
----
{"courseId":"MATH101","name":"Algebra","creditHours":3,"students":[{"studentId":
1,"name":"Donald P."},{"studentId":5,"name":"Hye E."}],"teacher":{"teacherId":10
1,"name":"Abdul J."},"Notes":"Prerequisite for Advanced Algebra"}


ERRORS
------
[{"schemaPath":"$","instancePath":"$","code":"JZN-00501","error":"JSON schema va
lidation failed"},{"schemaPath":"$.additionalProperties","instancePath":"$","cod
e":"JZN-00518","error":"invalid additional properties:  'Notes'"},{"schemaPath":
"$.additionalProperties","instancePath":"$.Notes","code":"JZN-00502","error":"JS
ON boolean schema was false"}]

Just as for the useFlexFields = true case, before importing you could choose to change the age field in the student input document for Luis J., to give a number value of 19 instead of the string value "Nineteen". And you might want to remove field Notes from the data or relax (lower) the value of minTypeFrequency to allow its inclusion.

Parent topic: Migrating From JSON To Duality