1. JSON-Relational Duality Developer's Guide
  2. Migrating From JSON To Duality
  3. JSON-To-Duality Importer

9.3 JSON-To-Duality Importer

The importer populates a duality view created by the converter with the documents stored in a JSON-type document set (more precisely, with the relational data needed to support such documents). Those stored documents correspond to a pre-existing external document set.

The importer is PL/SQL procedure DMBS_JSON_DUALITY.import.

  • Input: (1) An Oracle Database JSON document set, that is, a table with a single JSON-type column containing documents of a given kind. (2) The name of a duality view to populate.

  • Output: (1) A duality view with its underlying tables filled with the relational data that supports the same documents. (2) An error-log table that reports any documents that could not be imported.

You use procedure import once for each document set to be migrated.

See Also:

IMPORT Function in Oracle Database PL/SQL Packages and Types Reference for information about function DBMS_JSON_DUALITY.import.

Example 9-17 Create Error-Log Tables for Duality Views

This example creates error-log tables, *_error_log, for each of the duality views (argument dml_table_name). 

BEGIN
DBMS_ERRLOG.create_error_log(
  dml_table_name     => 'STUDENT',
  err_log_table_name => 'STUDENT_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     => 'COURSE',
  err_log_table_name => 'COURSE_ERR_LOG',
  skip_unsupported   => TRUE);
END;
/

Example 9-18 Import Documents Into Duality Views

This example uses PL/SQL procedure DBMS_JSON_DUALITY.import to import the JSON-type documents from the temporary transfer tables, *_tab, into the duality views created by the converter. It logs errors in the corresponding error-log tables, *_err_log. 

EXEC DBMS_JSON_DUALITY.import(
  table_name   => 'STUDENT_TAB',
  view_name    => 'STUDENT',
  err_log_name => 'STUDENT_ERR_LOG');

EXEC DBMS_JSON_DUALITY.import(
  table_name   => 'TEACHER_TAB',
  view_name    => 'TEACHER',
  err_log_name => 'TEACHER_ERR_LOG');

EXEC DBMS_JSON_DUALITY.import(
  table_name   => 'COURSE_TAB',
  view_name    => 'COURSE',
  err_log_name => 'COURSE_ERR_LOG');

See Also:

  • IMPORT Function in Oracle Database PL/SQL Packages and Types Reference for information about function DBMS_JSON_DUALITY.import.

  • DBMS_ERRLOG in Oracle Database PL/SQL Packages and Types Reference for information about procedure DBMS_ERRLOG.create_error_log

Parent topic: Migrating From JSON To Duality

9.3.1 Result of Importing After Default Conversion

The result of importing the student, teacher, and course document sets from the transfer tables after default conversion (in particular with useFlexFields:true) is shown. All documents are successfully imported, with all of their fields.

Example 9-19 Student Document Set (Migrator Output, useFlexFields:true)

Compare this with the input student document set, Example 9-1, which had no outliers. 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 mapping_table_course_root_to_student_root. Their values are the same as the values of fields courseNumber and studentId, respectively.

There are no other differences. Note too that mixed-type field grade is unchanged, as it is not an outlier. 

{"_id"       : 1,
 "_metadata" : {"etag" : "FF114F6623DEC5C9AAC00DBD6D7BD113",
                "asof" : "0000000000D3AE9D"},
 "age"       : 20,
 "name"      : "Donald P.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 1,
                  "name"             : "Algorithms",
                  "courseNumber"     : "CS101",
                  "grade"            : 90},
                 {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 1,
                  "name"             : "Data Structures",
                  "courseNumber"     : "CS102",
                  "grade"            : "TBD"},
                 {"ora$mapCourseId"  : "MATH101",
                  "ora$mapStudentId" : 1,
                  "name"             : "Algebra",
                  "courseNumber"     : "MATH101",
                  "grade"            : 90} ],
 "studentId" : 1}

{"_id"       : 2,
 "_metadata" : {"etag" : "C41C0F97AA5D9D3D44461DDBF6A80134",
                "asof" : "0000000000D3AE9D"},
 "age"       : 21,
 "name"      : "Elena H.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 2,
                  "name"             : "Algorithms",
                  "courseNumber"     : "CS101",
                  "grade"            : 75},
                 {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 2,
                  "name"             : "Data Structures",
                  "courseNumber"     : "CS102",
                  "grade"            : "TBD"},
                 {"ora$mapCourseId"  : "MATH102",
                  "ora$mapStudentId" : 2,
                  "name"             : "Calculus",
                  "courseNumber"     : "MATH102",
                  "grade"            : 95} ],
 "studentId" : 2}

{"_id"       : 3,
 "_metadata" : {"etag" : "1212696D37E948584540C8D094A4CCD2",
                "asof" : "0000000000D3AE9D" },
 "age"       : 20,
 "name"      : "Francis K.",
 "courses"   : [ {"ora$mapCourseId"  : "MATH103",
                  "ora$mapStudentId" : 3,
                  "name"             : "Advanced Algebra",
                  "courseNumber"     : "MATH103",
                  "grade"            : 83}],
 "studentId" : 3}

{"_id"       : 4,
 "_metadata" : {"etag" : "9EB8289EEE3FB4FCB40DC43C89C672E0",
                "asof" : "0000000000D3AE9D"},
 "age"       : 19,
 "name"      : "Georgia D.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 4,
                  "name"             : "Algorithms",
                  "courseNumber"     : "CS101",
                  "grade"            : 75},
                 {"ora$mapCourseId"  : "MATH102",
                  "ora$mapStudentId" : 4,
                  "name"             : "Calculus",
                  "courseNumber"     : "MATH102",
                  "grade"            : 85},
                 {"ora$mapCourseId"  : "MATH103",
                  "ora$mapStudentId" : 4,
                  "name"             : "Advanced Algebra",
                  "courseNumber"     : "MATH103",
                  "grade"            : 82} ],
 "studentId" : 4}

{"_id"       : 5,
 "_metadata" : {"etag" : "B488D4BD590CEBFFB3614924BE6A08DF",
                "asof" : "0000000000D3AE9D"},
 "age"       : 21,
 "name"      : "Hye E.",
 "courses"   : [ {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 5,
                  "name"             : "Data Structures",
                  "courseNumber"     : "CS102",
                  "grade"            : "TBD"},
                 {"ora$mapCourseId"  : "MATH101",
                  "ora$mapStudentId" : 5,
                  "name"             : "Algebra",
                  "courseNumber"     : "MATH101",
                  "grade"            : 97} ],
 "studentId" : 5}

{"_id"       : 6,
 "_metadata" : {"etag" : "4BD59A74DA1E87D52E2601E243F3C766",
                "asof" : "0000000000D3AE9D"},
 "age"       : 21,
 "name"      : "Ileana D.",
 "courses"   : [ {"ora$mapCourseId"  : "MATH103",
                  "ora$mapStudentId" : 6,
                  "name"             : "Advanced Algebra",
                  "courseNumber"     : "MATH103",
                  "grade"            : 95} ],
 "studentId" : 6}

{"_id"       : 7,
 "_metadata" : {"etag" : "AB71BFC4F00303D2C5187110FB45B68D",
                "asof" : "0000000000D3AE9D"},
 "age"       : 20,
 "name"      : "Jatin S.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 7,
                  "name"             : "Algorithms",
                  "courseNumber"     : "CS101",
                  "grade"            : 85},
                 {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 7,
                  "name"             : "Data Structures",
                  "courseNumber"     : "CS102",
                  "grade"            : "TBD"} ],
 "studentId" : 7}

{
 "_id"       : 8,
 "_metadata" : {"etag" : "30A793B67F6104493F68EB21C4031124",
                "asof" : "0000000000D3AE9D"},
 "age"       : 21,
 "name"      : "Katie H.",
 "courses"   : [ {"ora$mapCourseId"  : "CS102",
                  "ora$mapStudentId" : 8,
                  "name"             : "Data Structures",
                  "courseNumber"     : "CS102",
                  "grade"            : "TBD"},
                 {"ora$mapCourseId"  : "MATH103",
                  "ora$mapStudentId" : 8,
                  "name"             : "Advanced Algebra",
                  "courseNumber"     : "MATH103",
                  "grade"            : 90} ],
 "studentId" : 8}

{"_id"       : 9,
 "_metadata" : {"etag" : "1DD20C7695C0C140DE3E8C169905CD42",
                "asof" : "0000000000D3AE9D"},
 "age"       : 19,
 "name"      : "Luis F.",
 "courses"   : [ {"ora$mapCourseId"  : "CS101",
                  "ora$mapStudentId" : 9,
                  "name"             : "Algorithms",
                  "courseNumber"     : "CS101",
                  "grade"            : 75},
                 {"ora$mapCourseId"  : "MATH102",
                  "ora$mapStudentId" : 9,
                  "name"             : "Calculus",
                  "courseNumber"     : "MATH102",
                  "grade" : 95},
                 {"ora$mapCourseId"  : "MATH103",
                  "ora$mapStudentId" : 9,
                  "name"             : "Advanced Algebra",
                  "courseNumber"     : "MATH103",
                  "grade"            : 85} ],
 "studentId" : 9}

{"_id"       : 10,
 "_metadata" : {"etag" : "80EED24536C8B116CBC4699F105BC44C",
                "asof" : "0000000000D3AE9D"},
 "age"       : 20,
 "name"      : "Ming L.",
 "courses"   : [ {"ora$mapCourseId"  : "MATH102",
                  "ora$mapStudentId" : 10,
                  "name"             : "Calculus",
                  "courseNumber"     : "MATH102",
                  "grade"            : 95} ],
 "studentId" : 10}

Example 9-20 Teacher Document Set (Migrator Output, useFlexFields:true)

Compare this with the input teacher document set, Example 9-2, 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.) 

{"_id"           : 101,
 "_metadata"     : {"etag" : "D26B25FBD1E012B9F616F9709163A959",
                    "asof" : "0000000000D3AE97"},
 "name"          : "Abdul J.",
 "salary"        : 200000,
 "department"    : "Mathematics",
 "coursesTaught" : [ {"name"      : "Algebra",
                      "courseId"  : "MATH101",
                      "classType" : "Online"},
                     {"name"      : "Calculus",
                      "courseId"  : "MATH102",
                      "classType" : "In-person"} ],
 "phoneNumber"   : [ "222-555-011", "222-555-012" ]}

{"_id"           : 102,
 "_metadata"     : {"etag" : "20ABE18E3496CB34DF4AD58BA8EBB0AD",
                    "asof" : "0000000000D3AE97"},
 "name"          : "Betty Z.",
 "salary"        : 300000,
 "department"    : "Computer Science",
 "coursesTaught" : [ {"name"      : "Algorithms",
                      "courseId"  : "CS101",
                      "classType" : "Online"},
                     {"name"      : "Data Structures",
                      "courseId"  : "CS102",
                      "classType" : "In-person"} ],
 "phoneNumber"   : "222-555-022"}

{"_id"           : 103,
 "_metadata"     : {"etag" : "13B4619BEDDC2350BBEE186AEF14F77D",
                    "asof" : "0000000000D3AE97"},
 "name"          : "Colin J.",
 "salary"        : 220000,
 "department"    : "Mathematics",
 "coursesTaught" : [ {"name"      : "Advanced Algebra",
                      "courseId"  : "MATH103",
                      "classType" : "Online"} ],
 "phoneNumber"   : [ "222-555-023" ]}

{"_id"           : 104,
 "_metadata"     : {"etag" : "28E826A38C4301AA292F1EE1793B83D1",
                    "asof" : "0000000000D3AE97"},
 "name"          : "Natalie C.",
 "salary"        : 180000,
 "department"    : "Computer Science",
 "coursesTaught" : [ ],
 "phoneNumber"   : "222-555-044"}

Example 9-21 Course Document Set (Migrator Output, useFlexFields:true)

Compare this with the input course document set, Example 9-3, which had two outlier fields: Notes (rare) and creditHours (rare type). Both fields are present in the duality-view documents, even though they were outliers. Field Notes is present because it is stored in a flex column. Field creditHours is present because its outlier value for course MATH103 was converted from the string "3" to the number 3.

The only 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. 

{"_id"         : "CS101",
 "_metadata"   : {"etag" : "DE3FFA623F6F7DB22B86D80419ED5853",
                  "asof" : "0000000000D3AE94"},
 "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" : "81F7ED7E35A358E71EA7191C23A0C4C6",
                  "asof" : "0000000000D3AE94"},
 "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" : "4D86BE05F9C44EC2D179C8879235B2B2",
                  "asof" : "0000000000D3AE94"},
 "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" : "78D456BD3DBF44385CDDB97989497387",
                  "asof" : "0000000000D3AE94"},
 "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" : "135381BA439AB35714C8D6FDEA4AAC8E",
                  "asof" : "0000000000D3AE94"},
 "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"}

Related Topics

Parent topic: JSON-To-Duality Importer

9.3.2 Using the Importer, from useFlexFields:false Conversion

After trying to import, error-log tables are queried to show import errors and imported documents.

See Example 9-17 for the creation of the error-log tables used here, and Example 9-18 for the use of DBMS_JSON_DUALITY.import to import the document sets into the duality views.

Example 9-22 Show Error Log Entries for Student Import (useFlexFields:false)

This query selects the error messages for the student error log.

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

The same error is repeated ten times in the output, once for each failing student document (only the first is shown here).

ORA_ERR_NUMBER$  ORA_ERR_MESG$  ORA_ERR_TAG$
--------------------------------------------
40944
ORA-40944: Cannot insert into JSON Relational Duality View 'STUDENT': The input
JSON document is invalid.
JZN-00651: field 'grade' is unknown or undefined
Import Error
...
10 rows selected.

This query selects the erroneous student documents from the transfer table.Foot 1

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

This is the output. Only the first document selected is shown (student Donald P.). The others are similar. (The document is printed as a single line, but the line is split here for readability.) 

DATA
----
{"studentId":1,"name":"Donald P.","age":20,
 "courses":[{"courseNumber":"MATH101","name":"Algebra",
             "grade":90},
            {"courseNumber":"CS101","name":"Algorithms",
             "grade":90},
            {"courseNumber":"CS102","name":"Data Structures",
             "grade":"TBD"}]}
...
10 rows selected.

Querying the student duality view shows that nothing was imported: 

SELECT json_serialize(DATA PRETTY) FROM student;
no rows selected

Example 9-23 Show Error Log Entries for Teacher Import (useFlexFields:false)

This query selects the error messages for the teacher error log.

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

The same error is repeated four times in the output, once for each failing teacher document (only the first is shown here).

ORA_ERR_NUMBER$  ORA_ERR_MESG$  ORA_ERR_TAG$
--------------------------------------------
40944
ORA-40944: Cannot insert into JSON Relational Duality View 'TEACHER': The input
JSON document is invalid.
JZN-00651: field 'phoneNumber' is unknown or undefined
Import Error
...
4 rows selected.

This query selects the erroneous teacher documents from the transfer table.

SELECT * FROM "JANUS".teacher_tab
  WHERE ROWID IN (SELECT ora_err_rowid$ FROM teacher_err_log);

This is the output. Only the first document selected is shown (teacher Abdul J.). The others are similar. (The document is printed as a single line, but the line is split here for readability.) 

DATA
----
{"_id":101,"name":"Abdul J.",
 "phoneNumber":["222-555-011","222-555-012"],
 "salary":200000,"department":"Mathematics",
 "coursesTaught":[{"courseId":"MATH101","name":"Algebra","classType":"Online"},
                  {"courseId":"MATH102","name":"Calculus","classType":"In-person"}]}
...
4 rows selected.

Querying the teacher duality view shows that nothing was imported: 

SELECT json_serialize(DATA PRETTY) FROM teacher;
no rows selected

Example 9-24 Show Error Log Entries for Course Import (useFlexFields:false)

This query selects the error messages for the course error log.

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

Only one document is logged as failing import, the document with rare field Notes. 

ORA_ERR_NUMBER$  ORA_ERR_MESG$  ORA_ERR_TAG$
--------------------------------------------
40944
ORA-40944: Cannot insert into JSON Relational Duality View 'COURSE': The input
JSON document is invalid.
JZN-00651: field 'Notes' is unknown or undefined
Import Error

1 row selected.

This query selects the erroneous teacher documents from the transfer table.

SELECT * FROM "JANUS".course_tab
  WHERE ROWID IN (SELECT ora_err_rowid$ FROM course_err_log);

This is the output. Only the document with rare field Notes is selected. (The document is printed as a single line, but the line is split here for readability.) 

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

1 row selected.

Querying the course duality view shows that four of the five course documents — all except the one for MATH101 — were successfully imported. (The imported documents aren't shown here, to conserve space.) 

SELECT json_serialize(DATA PRETTY) FROM course;
...
4 rows selected.

See Also:

IMPORT Function in Oracle Database PL/SQL Packages and Types Reference for information about function DBMS_JSON_DUALITY.import.

Parent topic: JSON-To-Duality Importer


Footnote Legend

Footnote 1: JANUS is the database schema that owns the tables and views used in these examples.