8 From JSON To Duality
The JSON-To-Duality Migrator can migrate one or more existing sets of JSON documents to JSON-relational duality views. Its PL/SQL subprograms generate the views based on implicit document-content relations (shared content). By default, document parts that can be shared are shared, and the views are defined for maximum updatability.
Migration requires no supervision, but you should of course check the resulting duality views and their supported documents to verify their adequacy to your needs. You can modify the migration behavior to change the result.
There are two main use casesFoot 1 for the JSON-to-duality migrator:
-
Migrate an existing application and its sets of JSON documents from a document database to Oracle Database.
-
Create a new application, based on knowledge of the different kinds of JSON documents it will use (their structure and typing). The migrator can simplify this job, by automatically creating the necessary duality views.
The migrator has two components:
-
Converter: Create the database objects needed to support the original JSON documents: duality views and their underlying tables and indexes.
-
Importer: Import Oracle Database
JSON-type document sets that correspond to the original external documents into the duality views created by the converter.
Migration of existing stored document sets to sets supported by duality views consists of the following operations. You use the converter for the first three, and the importer for the fourth.
-
Validate: Check whether the existing document sets can be converted to duality-view support.
-
Normalize: Determine the relational tables needed for the duality views. Normalization is both across and within document sets: equivalent data in different document sets is shared by storing it in the same table.
-
Generate database objects:
-
Generate SQL scripts that create the necessary database objects: duality views and their underlying tables and indexes.
-
Optionally edit the scripts, to change the conversion behavior or the names of the views, tables, and indexes to be created.
-
Run the scripts to create the database objects.
-
-
Import: Import the existing documents into the duality views.
The converter is composed of these PL/SQL functions in package
DBMS_JSON_DUALITY:
-
infer_schemainfers the JSON schema that represents all of the input document sets. -
generate_schemaproduces the code to create the required database objects for each duality view. -
infer_and_generate_schemaperforms both operations.
The importer is PL/SQL procedure
DMBS_JSON_DUALITY.import. It populates a duality view
created by the converter with the documents from the corresponding input document set
(more precisely, with the relational data needed to support such documents).
To illustrate the use of the JSON-to-duality migrator we employ three small sets of documents that could be used by a school-administration application: student, teacher, and course documents. (A real application would of course likely have many more documents in its document sets, and the documents might be complex.) The pre-existing input document sets are shown in Example 8-1, Example 8-2, and Example 8-3.
Each of the document sets is loaded into a JSON-type
column, data, of a temporary transfer table from a
document-database dump file of documents of a given kind (e.g. student documents). The
transfer-table names have suffix _tab (e.g.,
student_tab for student documents). Column data is
the only column in a transfer table.
The migrator creates the corresponding duality views (e.g. view
student for student documents) and populates them with the data
from the transfer tables of stored documents. Once this is done, and you've
verified the adequacy of the duality views, the transfer tables are no longer
needed; you can drop them. The document sets are then no longer stored as such; their
now-normalized data is stored in the tables underlying the duality views.
Note:
There's no guarantee that migration to duality views preserves all pre-existing application data completely. In the process of normalization some data may be transformed, cast to different data types, or truncated to respect maximum size limits. Data that doesn't conform to the destination relational schema might then be rejected during import.
You need to check that all data has been successfully imported, by running verification tests and examining error logs.
You can ensure that your imported data is valid by comparing the documents in an input document set with those supported by the corresponding duality view, checking that the duality-view documents contain only the expected fields and possibly additional fields, and that no fields are missing or modified in unacceptable ways.
- School Administration Example, Migrator Input Documents
Existing student, teacher, and course document sets comprise the JSON-to-duality migrator input for the school-administration example. - JSON-To-Duality Converter
The converter can infer the inherent structure and typing of one or more sets of stored documents, as a JSON schema. Using the schema it can provide code to create the database objects needed to support the documents of each set: a duality view and its underlying tables and indexes. - JSON-To-Duality Importer
The importer populates a duality view created by the converter with the documents stored in aJSON-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.
See Also:
-
GENERATE_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.generate_schema -
INFER_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.infer_schema -
INFER_AND_GENERATE_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.infer_and_generate_schema -
IMPORT Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.import.
8.1 School Administration Example, Migrator Input Documents
Existing student, teacher, and course document sets comprise the JSON-to-duality migrator input for the school-administration example.
Note:
The document sets in the examples here are very small. In order to
demonstrate the handling of outlier (high-entropy) fields, we use a
minFrequency migrator configuration field value of 25,
instead of the default value of 5.
A field is an outlier for a given document set if it
occurs, or if any of its values occurs with a given type, in less than
minFrequency percent of the documents.
-
An outlier field that occurs rarely is either (1) retained in a flex column of a table underlying the duality view or (2) reported in an error log and not used in the duality view, according to the value of configuration field
useFlexFields. -
An outlier field whose value is rarely of a different type than usual is handled differently. Import tries to convert any such values of a rare type to the expected type for the field. Unsuccessful conversion is reported in an error log and the field is not used in the duality view.
See Fields Specifying Configuration Parameters for Inference and Generation for information about configuration fields minFrequency and
useFlexFields.
Example 8-1 Student Document Set (Migrator Input)
These are the student documents that we assume comprise an existing external document set that serves as input to the JSON-to-duality migrator. There are no outlier fields; that is, there are no fields that are rare or whose values have rare types.
The documents all have the same fields, but note that field
grade is of mixed type: string and number. Neither type
occurs rarely as a grade value, however (in less than 25% of the
student documents, 25 being the minFrequency value
we use for the examples here).
{"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"} ]}
{"studentId" : 2,
"name" : "Elena H.",
"age" : 21,
"courses" : [ {"courseNumber" : "MATH102",
"name" : "Calculus",
"grade" : 95},
{"courseNumber" : "CS101",
"name" : "Algorithms",
"grade" : 75},
{"courseNumber" : "CS102",
"name" : "Data Structures",
"grade" : "TBD"} ]}
{"studentId" : 3,
"name" : "Francis K.",
"age" : 20,
"courses" : [ {"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"grade" : 83} ]}
{"studentId" : 4,
"name" : "Georgia D.",
"age" : 19,
"courses" : [ {"courseNumber" : "MATH102",
"name" : "Calculus",
"grade" : 85},
{"courseNumber" : "CS101",
"name" : "Algorithms",
"grade" : 75},
{"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"grade" : 82} ]}
{"studentId" : 5,
"name" : "Hye E.",
"age" : 21,
"courses" : [ {"courseNumber" : "MATH101",
"name" : "Algebra",
"grade" : 97},
{"courseNumber" : "CS102",
"name" : "Data Structures",
"grade" : "TBD"} ]}
{"studentId" : 6,
"name" : "Ileana D.",
"age" : 21,
"courses" : [ {"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"grade" : 95}]}
{"studentId" : 7,
"name" : "Jatin S.",
"age" : 20,
"courses" : [ {"courseNumber" : "CS101",
"name" : "Algorithms",
"grade" : 85},
{"courseNumber" : "CS102",
"name" : "Data Structures",
"grade" : "TBD"} ]}
{"studentId" : 8,
"name" : "Katie H.",
"age" : 21,
"courses" : [ {"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"grade" : 90},
{"courseNumber" : "CS102",
"name" : "Data Structures",
"grade" : "TBD"} ]}
{"studentId" : 9,
"name" : "Luis F.",
"age" : 19,
"courses" : [ {"courseNumber" : "MATH102",
"name" : "Calculus",
"grade" : 95},
{"courseNumber" : "CS101",
"name" : "Algorithms",
"grade" : 75},
{"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"grade" : 85} ]}
{"studentId" : 10,
"name" : "Ming L.",
"age" : 20,
"courses" : [ {"courseNumber" : "MATH102",
"name" : "Calculus",
"grade" : 95} ]}Compare this with the student document set migrated using the default conversion, Example 8-19. There are no differences, beyond the addition of fields needed for duality-view support generally.
Example 8-2 Teacher Document Set (Migrator Input)
These are the teacher documents that we assume comprise an existing external document set that serves as input to the JSON-to-duality migrator. There are no outlier fields; that is, no fields are rare or have values with rare types.
The documents have the same fields, but note that field
phoneNumber is of mixed type: string and array (array of
strings). Neither type occurs rarely as a phoneNumber value,
however (in less than 25% of the teacher documents, 25 being the
minFrequency value we use for the examples here).
(Note also that the value of one occurrence of field
coursesTaught is an empty array.)
{"_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"} ]}
{"_id" : 102,
"name" : "Betty Z.",
"phoneNumber" : "222-555-022",
"salary" : 300000,
"department" : "Computer Science",
"coursesTaught" : [ {"courseId" : "CS101",
"name" : "Algorithms",
"classType" : "Online"},
{"courseId" : "CS102",
"name" : "Data Structures",
"classType" : "In-person"} ]}
{"_id" : 103,
"name" : "Colin J.",
"phoneNumber" : [ "222-555-023" ],
"salary" : 220000,
"department" : "Mathematics",
"coursesTaught" : [ {"courseId" : "MATH103",
"name" : "Advanced Algebra",
"classType" : "Online"} ]}
{"_id" : 104,
"name" : "Natalie C.",
"phoneNumber" : "222-555-044",
"salary" : 180000,
"department" : "Computer Science",
"coursesTaught" : []}Compare this with the teacher document set migrated using the default conversion, Example 8-20. There are no differences, beyond the addition of fields needed for duality-view support generally.
Example 8-3 Course Document Set (Migrator Input)
These are the course documents that we assume comprise an existing
external document set that serves as input to the JSON-to-duality migrator. There
two outlier fields, Notes and
creditHours:
-
Field
Notesis an outlier because it occurs in only one course document (one out of five, 20%, less than theminFrequencyvalue of25that we use for the examples here). -
Field
creditHoursis an outlier because it has a string value in less than 25% of the documents; it has a number value in the other documents.
{"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"}
{"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" : [ {"studentId" : 2, "name" : "Elena H."},
{"studentId" : 10, "name" : "Ming L."},
{"studentId" : 9, "name" : "Luis F."},
{"studentId" : 4, "name" : "Georgia D."} ],
"teacher" : {"teacherId" : 101, "name" : "Abdul J."}}
{"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" : [ {"studentId" : 1, "name" : "Donald P."},
{"studentId" : 2, "name" : "Elena H."},
{"studentId" : 4, "name" : "Georgia D."},
{"studentId" : 9, "name" : "Luis F."},
{"studentId" : 7, "name" : "Jatin S."} ],
"teacher" : {"teacherId" : 102, "name" : "Betty Z."}}
{"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" : [ {"studentId" : 1, "name" : "Donald P."},
{"studentId" : 2, "name" : "Elena H."},
{"studentId" : 5, "name" : "Hye E."},
{"studentId" : 7, "name" : "Jatin S."},
{"studentId" : 8, "name" : "Katie H."} ],
"teacher" : {"teacherId" : 102, "name" : "Betty Z."}}
{"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : "3",
"students" : [ {"studentId" : 3, "name" : "Francis K."},
{"studentId" : 4, "name" : "Georgia D."},
{"studentId" : 8, "name" : "Katie H."},
{"studentId" : 9, "name" : "Luis F."},
{"studentId" : 6, "name" : "Ileana D."} ],
"teacher" : {"teacherId" : 103, "name" : "Colin J."}}Compare this with the course document set migrated using the default
conversion, Example 8-21. There are no differences, beyond the addition of fields needed for duality-view
support generally. In particular, outlier fields Notes (rare) and
creditHours (rare type) are both present after migration,
Notes because it is stored in a flex column, and
creditHours because its outlier value for course
MATH103 is converted from the string "3" to
the number 3.
Related Topics
Parent topic: From JSON To Duality
8.2 JSON-To-Duality Converter
The converter can infer the inherent structure and typing of one or more sets of stored documents, as a JSON schema. Using the schema it can provide code to create the database objects needed to support the documents of each set: a duality view and its underlying tables and indexes.
Overview of JSON-To-Duality Converter
The converter is composed of these PL/SQL
functions in package DBMS_JSON_DUALITY:
-
infer_schema: Infer a relational schema that represents the documents in the existing document sets.-
Input: A JSON object whose members specify configuration parameters for the inference operation — see Fields Specifying Configuration Parameters for Inference and Generation.
-
Output: a JSON Schema document that specifies the inferred relational schema. If no such schema can be found then an error is raised saying that the converter can't create duality views corresponding to the input document sets.
-
-
generate_schema: Produce the SQL data-definition language (DDL) scripts to generate the necessary duality views and their underlying tables and indexes.-
Input: the JSON schema output from function
infer_schema. -
Output: DDL scripts to create the needed database objects.
-
-
infer_and_generate_schema: Doinfer_schemaandgenerate_schematogether.-
Input: same as
infer_schema. -
Output: same as
generate_schema.
-
The generated code creates the appropriate duality views; their underlying tables; primary, unique, and foreign key constraints; indexes; and default values — everything needed to support the original document sets.
After you've run the generated code to create the database objects needed to support a given document set, you can use the JSON-To-Duality Importer to populate the new duality view with the documents (more precisely, with the relational data needed to support the input document set).
Fields Specifying Configuration Parameters for Inference and Generation
The following configuration fields can be used in the JSON object that
is passed to functions infer_schema and
infer_and_generate_schema for inferring the relational schema.
All except field tableNames are optional. The use of
any other fields besides those listed here raises an error.
Some of the field values are also used for the DDL generation provided
by generate_schema and infer_and_generate_schema.
But for generate_schema this information is provided by a PL/SQL
parameter whose value is a JSON schema produced by infer_schema,
not by such fields.
-
ingestLimit(Optional) — The maximum number of documents to be analyzed in each document set.The default value is 100,000.
-
minFrequency(Optional) — The minimum frequency for a field not to be considered an outlier (high-entropy).More precisely, a field is an outlier for a given document set if it occurs, or if any of its values occurs with a given type, in less than
minFrequencypercent of the documents.For example, in the input course documents:
-
Field
Notesis an outlier because it occurs in less thanminFrequencypercent of the documents. -
Field
creditHoursis an outlier because it has a string value in less thanminFrequencypercent of the documents. (It has a number value in the other documents.)
The default
minFrequencyvalue is5, meaning that a field that occurs in less than five percent of a view's documents, or a field that occurs with a value of some type in less than five percent of a view's documents, is considered high-entropy.How a rare field is handled is determined by the value of field
useFlexFields.Note:
In the examples presented here, which involve very few documents in each document set, we use
25as theminFrequencyvalue, in order to demonstrate the determination and handling of outliers. -
-
outputFormat(Optional) — A string whose value defines the format of the output data definition language (DDL) script.The default value is
"executable", which means you can execute the DDL script directly: it uses PL/SQLEXECUTE IMMEDIATE. The other possible value is"standalone", which means you can use the DDL script in a SQL script that you run separately.If the generated DDL is larger than 32K bytes then you must use
"standalone; otherwise, an error is raised when you useEXECUTE IMMEDIATE. An"executable"DDL script can be too large if the input data sets are themselves very large or they have many levels of nested values. -
sourceSchema(Optional) — A string whose value is the name of the database schema (user) that owns the input tables (tableNames).If not provided then the database schema used to identify the input tables is the one that's current when the DDL is generated (not when it is executed).
-
tableNames(Required) — An array of strings naming the Oracle Database transfer tables that correspond to the original external document sets. Each table must have aJSON-type column (it need not be nameddata), which stores the documents of a given document set. -
tablespace(Optional) — A string whose value is the name of the tablespace to use for all of the tables underlying the duality views.If not provided then no tablespace is specified in the output DDL. This means that the tablespace used is the one that's current at the time the DDL code is executed (not when it is generated).
-
targetSchema(Optional) — A string whose value is the name of the database schema (user) that will own the output database views (viewNames).If not provided then no database schema is specified in the output DDL; the names of the database objects to be created are unqualified. This means that the schema used is the one that's current at the time the DDL code is executed (not when it is generated).
-
updatability(Optional) — A Boolean value determining whether the duality views to be generated are to be updatable (true) or not (false). Whentrue, annotations are set for maximum updatability of each view. Whenfalseall of the views created are read-only.The default value is
true. -
useFlexFields(Optional) — A Boolean value determining whether flex columns are to be added to the tables underlying the duality views. Flex columns are used to store unrecognized fields in an incoming document to be inserted or updated.When
useFlexFieldsistrue, for each duality view<view-name>, a flex column namedora$<view-name>_flexis added to each table that directly underlies the top-level fields of an object in the supported documents. (The fields stored in a given flex column are unnested to that object.)The default value is
true.Besides providing for that usual flex-column runtime behavior, when
useFlexFieldsistruethe converter also places, in the flex columns, some fields from the input document sets that can't be based on a scalar SQL column: (1) fields that are outliers because they occur rarely, and (2) non-outlier fields of mixed type (that is, with no type occurring rarely).Foot 2 WhenuseFlexFieldsisfalsesuch fields are simply reported in an error log and not used in the duality views. -
viewNames(Optional) — An array of strings naming the duality views to be created, one for each document set.If not provided then the
tableNameswith_dualityappended are used as the view names. For example the name of the view corresponding to the documents in tablefoodefaults tofoo_duality.
If field viewNames is provided then its array length
must be the same as that of field tableNames; otherwise, an error
is raised (not logged).
The Converter Can Add Some Duality-View Fields and Columns
In some cases the converter creates fields and columns for a duality view definition that are not in the original document set.
-
Document-identifer field
_idis generated for each document, if it is not already present in the input documents.A duality view must have a top-level
_idfield (the document identifier), which corresponds to the primary-key column(s) of the view's root table. If a document input to the converter already has a top-level_idfield, then its associated column is in the root table and is chosen as the table's primary-key column. -
Document-handling field
_metadatais generated and maintained for each document, to record its content-hash version (ETAG) and its latest system change number (SCN). This field is not part of the document content per se (payload) . -
Other generated field and column names always have the prefix
ora$.
A duality view definition needs explicit fields for the primary-key columns of each of its underlying tables, and this is another case where new fields are sometimes added.
This is the case for views course and
student, which use an underlying mapping table,
mapping_table_course_root_to_student_root, which has two
primary-key columns, map_course_id and
map_student_id. These have foreign-key references to the
primary-key columns, course_id and student_id, of
the course and student tables, course_root and
student_root.
At the place where the mapping table is used in the view definitions,
each of its primary-key columns (map_course_id and
map_student_id) must be present, with a field assigned to it.
These fields are present in the documents supported by the view. The converter uses
prefix ora$ for their names, with the remainder taken from the
column names (converted to camelCase, without underscore separators):
ora$mapCourseId and ora$mapStudentId.
When configuration field useFlexFields is
true, the converter adds flex columns to the tables underlying
the duality views it creates. Each flex column is named
ora$<view-name>_flex,
where <view-name> is the name of the duality view
where it is defined — see Fields Specifying Configuration Parameters for Inference and Generation. (You might mistake this for a field name in the view definition, but it's a
column name; the name does not appear in the documents supported by the view.)
- Before Using the Converter: Create Database Document Sets and JSON Schemas
Before using the JSON-to-duality converter you need to createJSON-type document sets in Oracle Database from the original external document sets. The input to the converter for each set of documents is an Oracle Database table with a single column ofJSONdata type. - Overview of Using the JSON-To-Duality Converter
The converter takes, as input, tablesstudent_tab,teacher_tab, andcourse_tab, withJSON-type columns holding your original student, teacher, and course document sets. It infers duality viewsstudent,teacher, andcourseand generates PL/SQL code to create the views and their underlying relational data. - Using the Converter, Default Behavior
The student-teacher-course use case is used to illustrate the use of the JSON-to-duality converter with its default values (except forminFrequency). In particular, configuration fielduseFlexFieldsistrue. The database objects needed to support the document sets are inferred and the DDL to construct them is generated. - Using the Converter with useFlexFields:false
If you use the converter with configuration fielduseFlexFieldsset tofalsethen, during import, an error is logged for a field that can't be stored in a simple SQL scalar column, instead of the field being stored in a flex column.
Related Topics
See Also:
-
GENERATE_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.generate_schema -
INFER_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.infer_schema -
INFER_AND_GENERATE_SCHEMA Function in Oracle Database PL/SQL Packages and Types Reference for information about function
DBMS_JSON_DUALITY.infer_and_generate_schema
Parent topic: From JSON To Duality
8.2.1 Before Using the Converter: Create Database Document Sets and JSON Schemas
Before using the JSON-to-duality converter you need to create
JSON-type document sets in Oracle Database from the original external
document sets. The input to the converter for each set of documents is an Oracle Database
table with a single column of JSON data type.
You can export JSON document sets from a document database and import them
into JSON-type columns using various tools provided by Oracle and
document databases. (MongoDB command-line tools mongoexport and
mongoimport provide one way to do this.)
We assume that each of the student, teacher, and course document sets has
been thus loaded into a JSON-type column, data, of a
temporary transfer table (e.g. course_tab for course documents) from a
document-database dump file of documents of the given kind (e.g. course documents). This
is shown in Example 8-4.
The transfer tables for the input document sets are all you need to use the
converter. But it's a good idea to also create a JSON schema as a model, or
template representing each input document set. This provides an overview of a particular
kind of documents, in particular their structure and typing. Example 8-5 illustrates this for the course document set in column
course_tab.data.
Comparing a JSON schema for an input document set (in a transfer table)
against a JSON schema for the duality view that's expected to replace it can highlight
fields that the converter has identified as problematic, and that were thus relegated to
a flex column (or logged as errors, if useFlexFields was
false). For example, you can compare the schemas from Example 8-5 and Example 8-13.
It can also be worthwhile to create a JSON schema-format data guide for an input document set. This is a JSON schema that can include statistical information about the specific content; in particular, for each field, in what percentage of documents it occurs, in what percentage of documents it has values of which types, and the range of values for each type. Fields that are stored in a flex column generally have low frequency or values of mixed type. See Example 8-6 and the resulting data guides, Example 8-7 and Example 8-8
Example 8-4 Create an Oracle Document Set (Course) From a JSON Dump File.
This example creates an Oracle Database external table,
dataset_dump_course, from a JSON dump file of a set of course
documents, course.json. It then creates table
course_tab with JSON-type column data.
Finally, it imports the course documents into temporary transfer table
course_tab, which can be used as input to the JSON-relational
converter.
The documents in course_tab.data are those shown in
Example 8-3.
(Similarly student and teacher document sets are loaded into transfer
tables student_tab and teacher_tab from external
tables dataset_dump_student and
dataset_dump_teacher created from dump files
student.json and teacher.json,
respectively.)
CREATE TABLE dataset_dump_course (data JSON)
ORGANIZATION EXTERNAL
(TYPE ORACLE_BIGDATA
ACCESS PARAMETERS (com.oracle.bigdata.fileformat = jsondoc)
LOCATION (data_dir:'course.json'))
PARALLEL
REJECT LIMIT UNLIMITED;
CREATE TABLE course_tab AS SELECT * FROM dataset_dump_course;
SELECT json_serialize(data PRETTY) FROM course_tab;Note:
Oracle Database supports the use of textual JSON objects that represent
nonstandard-type scalar JSON values. For example, the extended object
{"$numberDecimal" : 31} represents a JSON scalar value of
the nonstandard type decimal number, and when interpreted as such it is
replaced by a decimal number in Oracle's native binary JSON format, OSON.
Some non-Oracle databases also use such extended objects. If such an external extended object is a format recognized by Oracle then, when ingested, the object is replaced by the corresponding Oracle scalar JSON value. If the format isn't supported by Oracle then the extended object is retained as such, that is, as an object.
See Textual JSON Objects That Represent Extended Scalar Values in Oracle Database JSON Developer’s Guide for information about Oracle support for extended objects.
Example 8-5 Create a JSON Schema For Course Input Document Set
This example uses PL/SQL function
DBMS_JSON_SCHEMA.describe to create a JSON schema
that describes the input set of course documents, which are stored in transfer table
course_tab.Foot 3 The
describe output is saved in table
course_tab_schema.
(Some insignificant whitespace is removed from the JSON data shown here, to facilitate readability.)
CREATE TABLE course_tab_schema AS
SELECT DBMS_JSON_SCHEMA.describe('COURSE_TAB') AS data FROM DUAL;
SELECT json_serialize(data PRETTY ORDERED) FROM course_tab_schema;{"dbObject" : "JANUS.COURSE_TAB",
"dbObjectType" : "table",
"title" : "COURSE_TAB",
"type" : "object",
"properties" :
{"DATA" :
{"allOf" :
[ {"title" : "DATA",
"type" : "object",
"properties" :
{"Notes" : {"maxLength" : 64,
"extendedType" : [ "string", "null" ]},
"courseId" : {"maxLength" : 32,
"extendedType" : [ "string", "null" ]},
"creditHours" : {"extendedType" : [ "number", "string", "null" ]},
"name" : {"maxLength" : 32,
"extendedType" : [ "string", "null" ]},
"students" :
{"type" : "array",
"items" :
{"type" : "object",
"properties" :
{"name" : {"maxLength" : 32,
"extendedType" : [ "string", "null" ]},
"studentId" : {"extendedType" : "number"}}}},
"teacher" :
{"type" : "object",
"properties" :
{"name" : {"maxLength" : 32,
"extendedType" : [ "string", "null" ]},
"teacherId" : {"extendedType" : "number"}}}}} ]}}}See Also:
DESCRIBE Function in Oracle Database PL/SQL
Packages and Types Reference for information
about function DBMS_JSON_SCHEMA.describe
Example 8-6 Create JSON Data Guides For Student and Course Document Set
This example uses Oracle SQL function json_dataguide to
create data guides for the input student and course document sets. These are JSON
schemas that can be used to validate their documents.
Parameter DBMS_JSON.FORMAT_SCHEMA ensures that
the data guide is usable for validating. Parameter
DBMS_JSON.PRETTY pretty-prints the result. Parameter
DBMS_JSON.GATHER_STATS provides the data guide with
statistical fields such as o:frequency, which specifies the
percentage of documents in which a given field occurs or has a given type of
value.
SELECT json_dataguide(data,
DBMS_JSON.FORMAT_SCHEMA,
DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS)
FROM student_tab;
SELECT json_dataguide(data,
DBMS_JSON.FORMAT_SCHEMA,
DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS)
FROM course_tab;
The resulting data guides are presented in Example 8-7 and Example 8-8.
See Also:
DBMS_JSON Constants in Oracle Database PL/SQL
Packages and Types Reference
for information about constants DBMS_JSON.FORMAT_SCHEMA,
DBMS_JSON.GATHER_STATS, and
DBMS_JSON.PRETTY
Example 8-7 JSON Data Guide For Input Student Document Set
This data guide summarizes the input set of student documents stored in
transfer table student_tab. (Some insignificant whitespace is
removed here, to facilitate readability.)
{"type" : "object",
"o:length" : 1,
"o:frequency" : 100,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10,
"properties" :
{"age" :
{"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "age",
"o:frequency" : 100,
"o:low_value" : 19,
"o:high_value" : 21,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10},
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 10},
"courses" :
{"type" : "array",
"o:length" : 1,
"o:preferred_column_name" : "courses",
"o:frequency" : 100,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10,
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 10},
"grade" :
{"oneOf" : [ {"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "grade",
"o:frequency" : 100,
"o:low_value" : 75,
"o:high_value" : 97,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10},
{"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "grade",
"o:frequency" : 50,
"o:low_value" : "TBD",
"o:high_value" : "TBD",
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10} ]},
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 10}}}},
"studentId" :
{
"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "studentId",
"o:frequency" : 100,
"o:low_value" : 1,
"o:high_value" : 10,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 10}}}Note that field grade has a type that
is either (1) a number, with o:frequency
100, or (2) a string, with
o:frequency
50. This means that a numeric grade appears in 100%
of the documents, and a string grade appears in 50% of the documents.
Field grade is thus a mixed-type field, and it
is not an outlier: neither of its types is used rarely across the document
set, as determined by configuration parameter minFrequency.
minFrequency tests the percentage of documents where a
field of a given type is present across the document set. With each of its
types (number and string), field grade is used in more than
minFrequency percent of the student documents. This presence
amply satisfies the requirement of minimum presence across all documents.
As a non-outlier mixed-type field, grade is thus a good
candidate for having its own column of JSON data type, and of that
column having its own JSON schema applied to it as a validating check
constraint, to require the value to always be either a string or a
number.
Example 8-8 JSON Data Guide For Input Course Document Set
This data guide summarizes the input set of student documents stored in transfer
table course_tab. (Some insignificant whitespace is removed, to
facilitate readability.)
{"type" : "object",
"o:length" : 1,
"o:frequency" : 100,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5,
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
"teacher" :
{"type" : "object",
"o:length" : 1,
"o:preferred_column_name" : "teacher",
"o:frequency" : 100,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5,
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
"teacherId" :
{"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "teacherId",
"o:frequency" : 100,
"o:low_value" : 101,
"o:high_value" : 103,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5}}},
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
"students" :
{"type" : "array",
"o:length" : 1,
"o:preferred_column_name" : "students",
"o:frequency" : 100,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5,
"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
"studentId" :
{"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "studentId",
"o:frequency" : 100,
"o:low_value" : 1,
"o:high_value" : 10,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5}}}},
"creditHours" :
{"oneOf" :
[ {"type" : "number",
"o:length" : 2,
"o:preferred_column_name" : "creditHours",
"o:frequency" : 80,
"o:low_value" : 3,
"o:high_value" : 5,
"o:num_nulls" : 0,
"o:last_analyzed" : "2024-04-05T23:43:33",
"o:sample_size" : 5},
{"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" : "2024-04-05T23:43:33",
"o:sample_size" : 5} ]}}}Field Notes occurs in only 20% of the documents (field
o:frequency is 20), which (because
configuration field minFrequency is 25 for our
examples) means it's an outlier field, and will thus be removed from the
data.
Field creditHours has a type that is
either (1) a number, with o:frequency
80, or (2) a string, with
o:frequency
20. It is thus a mixed-type field. Because the string occurrence is
less than the value of configuration field minFrequency (25), it is
also an outlier.
A field that doesn't occur rarely but has a type that occurs rarely is
not removed from the data. Instead, the importer tries to convert the string
value to a number. In the course duality view definition
the underlying column for field creditHours
course has SQL type NUMBER.
Because the string value "3" can be converted to a
number (3), the outlier creditHours occurrence is
imported successfully, using the numeric value. If the string value were instead
"three" then the importer would raise an error, because that
can't be converted to a number.
Related Topics
See Also:
-
Migrate Application Data from MongoDB to Oracle Database in Oracle Database API for MongoDB for information about using commands
mongoexportandmongoimportto migrate -
Loading External JSON Data in Oracle Database JSON Developer’s Guide for loading data from a document-database dumpfile into Oracle Database
-
Validating JSON Documents with a JSON Schema for information about using JSON schemas to constrain or validate JSON data
-
json-schema.org for information about JSON Schema
-
JSON Data Guide in Oracle Database JSON Developer’s Guide
Parent topic: JSON-To-Duality Converter
8.2.2 Overview of Using the JSON-To-Duality Converter
The converter takes, as input, tables student_tab,
teacher_tab, and course_tab, with
JSON-type columns holding your original student, teacher, and course
document sets. It infers duality views student, teacher,
and course and generates PL/SQL code to create the views and their
underlying relational data.
PL/SQL function DBMS_JSON_DUALITY.infer_schema infers the
duality views and their underlying tables; function
DBMS_JSON_DUALITY.generate_schema generates the DDL to create them;
and function DBMS_JSON_DUALITY.infer_and_generate_schema does both. We
use infer_and_generate_schema here.
By default, configuration field useFlexFields is
true, which means that fields that can't be based on a scalar SQL
column are stored in a flex column as JSON-type data. Such fields,
across a given document set, are either (1) outliers because they are relatively
rare, or (2) of mixed type but with no type used rarely. Mixed-type fields that
are outliers because their values are only rarely of a different type than usual are not
stored in a flex column. Instead, import tries to convert the rare-type occurrences to
the common type for the field.
After you run infer_and_generate_schema, find which, if
any, of the original fields appear to be missing because they will be stored in a
flex column, that is, they aren't associated with a non-JSON SQL column.
Then you can decide whether to leave them in the flex column, delete them, or change their values (for example, so they always have the same scalar type. In particular, it's good to identify which fields are outliers and which are not. Outliers are sometimes accidental — a string numeral where you really wanted a number, for example. Non-outlier mixed type fields (e.g. number and string) are more often intended as such, and expected by an existing application.
A non-outlier field of mixed type is a good candidate to move out of a flex
column into its own
JSON-type column. In effect, it has been normalized to a type
(e.g. number-or-string) that SQL doesn't have. You can enforce this regularity, if
intended, by imposing a small JSON schema on the column: a schema that just constrains
the value to be either a JSON number or a JSON string.
How do you find which fields appear to be missing, whether outlier or not? There are a few ways:
-
Examine the output DDL code, checking the original set of fields against the field columns to be created.
If
useFlexFieldsistrue(the default) then fields for which there is no corresponding column will be stored in a flex column. If it isfalsethey'll simply be missing from documents supported by the duality view. -
Compare a JSON schema that you create for an original document set against a JSON schema that you create for the corresponding duality view. (This assumes that you've already run the DDL to create the tables and views.)
For example, compare the schema for input table
course_tab(Example 8-5) with the schema for duality viewcourse(Example 8-13).If
useFlexFieldsistruethen the two schemas should be functionally equivalent. If it isfalsethen outlier and mixed-type fields will be missing from the JSON schema for the duality view. -
Examine a schema-format JSON data guide created from an original document set (e.g, input table
student_tab).Check the
o:frequencyvalue for each missing field, to see whether or not the field is an outlier. Check also thetypevalue for each missing field, to see whether or not it is mixed-type.
Based on what you decide, make appropriate changes to the DDL, and run it to generate the duality views you really want.
Related Topics
Parent topic: JSON-To-Duality Converter
8.2.3 Using the Converter, Default Behavior
The student-teacher-course use case is used to illustrate the use of the
JSON-to-duality converter with its default values (except for
minFrequency). In particular, configuration field
useFlexFields is true. The database objects needed to
support the document sets are inferred and the DDL to construct them is
generated.
The input document sets are stored in database tables
student_tab, teacher_tab, and
course_tab (field tableNames) in the current
database schema (default). The duality views to be generated are
student, teacher, and course,
respectively (field viewNames).
The default value of configuration field useFlexFields is
true, which allows the resulting duality views to support some
scalar fields whose values don't consistently correspond to single SQL scalar data
types.
The minimum frequency (configuration field minFrequency
value) used in the examples here is 25 (not the default value of
5), so a field that occurs, or occurs with a value of a particular
type, in less than 25% of a view's documents is considered an outlier
(high-entropy).
Note:
The document sets in the examples here are very small. In order to
demonstrate the handling of outlier (high-entropy) fields, we use a
minFrequency migrator configuration field value of 25,
instead of the default value of 5.
A field is an outlier for a given document set if it
occurs, or if any of its values occurs with a given type, in less than
minFrequency percent of the documents.
-
An outlier field that occurs rarely is either (1) retained in a flex column of a table underlying the duality view or (2) reported in an error log and not used in the duality view, according to the value of configuration field
useFlexFields. -
An outlier field whose value is rarely of a different type than usual is handled differently. Import tries to convert any such values of a rare type to the expected type for the field. Unsuccessful conversion is reported in an error log and the field is not used in the duality view.
See Fields Specifying Configuration Parameters for Inference and Generation for information about configuration fields minFrequency and
useFlexFields.
If you execute the generated DDL code then the duality views, their underlying tables, and indexes are created. You can then create a JSON schema describing each duality view and compare that with the JSON schema that describes the corresponding input document set.
The JSON schema for the course document set (stored in transfer table
course_tab) is shown in Example 8-13. Comparing that with the JSON schema for the input course document set, Example 8-5 shows that the document fields correspond, with the exception of the
two outlier fields Notes and creditHours:
-
Field
Notesis missing from the documents supported by the duality view. This is because it occurs in less thanminFrequency(25) percent of the documents. -
Field
creditHoursis not missing, but its type has changed fromnumberorstringto justnumber. This is because (1) a string value is present in less thanminFrequency(25) percent of the documents and (2) the only string values are numeric strings, which the duality view converts to numbers. (The single string value is"3".)
Such comparison can help decide how you might want to change some of the
documents or whether and how you might want to change the configuration fields used to
infer and generate the database objects. For example, if you want to be sure to preserve
the rare occurrence of field Notes or the rare use of a string value
for field creditHours, then you can change the duality-view definition
to give each of those fields its own JSON-type column.
However, 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 JSON schemas can suggest things you might want to change, but it isn't a substitute for comparing documents. After you import the original documents into the duality views you can and should compare documents.
When comparing JSON schemas for transfer table course_tab
and duality view course, or just by looking at the definition of view
course, you'll notice that the documents to be supported by the
view also contain the generated fields _id,
ora$mapCourseId, and ora$mapStudentId. See The Converter Can Add Some Duality-View Fields and Columns.
Example 8-9 Infer Database Objects and Generate Their DDL (Configured With Flex Columns)
DECLARE
schema_sql CLOB;
BEGIN
schema_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
"minFrequency" : 25}'));
DBMS_OUTPUT.put_line('DDL Script: ');
DBMS_OUTPUT.put_line(schema_sql);
END;
/These optional fields are absent here:
-
Field
errorLog, which is anyway ignored because fielduseFlexFieldsis (by default)true. - Field
ingestLimit, which means that its value is100000(default), so each document set can have no more than 100,000 documents. -
Field
outputFormat, which means that its value isexecutable(default), so the DDL script can be executed directly using PL/SQLEXECUTE IMMEDIATE. -
sourceSchema, which means that the views are to be owned by the user (database schema) that is logged in wheninfer_and_generate_schemais invoked, that is, when the DDL code is generated. -
tablespace, which means that the tables underlying the views are to use the tablespace that's current when the generated DDL code is executed. -
targetSchema, which means that the views are to be owned by the user (database schema) logged in when the generated DDL code is executed. -
Field
updatability, which means that its value istrue(default), so the views are created with maximum updatability. -
Field
useFlexFields, which means that its value istrue(default), so (1) fields that are outliers because they aren't present in at leastminFrequencypercent of the documents of a given type, and (2) non-outlier fields that are of mixed type but with each type used in at leastminFrequencypercent of the documents, are stored in flex columns.
Note:
If you use configuration field outputFormat with a
value of standalone, instead of the default value of
executable, then function
infer_and_generate returns the generated DDL as a SQL
script without wrapping it with EXECUTE IMMEDIATE. That can be
handier, for example, if you want to modify the script.
The resulting DDL is shown in Example 8-10 and Example 8-11.
See Also:
INFER_AND_GENERATE_SCHEMA
Function in Oracle Database PL/SQL
Packages and Types Reference for information about
function DBMS_JSON_DUALITY.infer_and_generate_schema
Example 8-10 DDL Generated For Tables (useFlexFields:true)
This is the DDL code (generated using configuration field
useFlexFields:true) that creates the tables underlying the
duality views. It also creates foreign-key constraints and indexes. The DDL that
defines the views is shown in Example 8-11.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE map_course_root_to_student_root(
map_course_id varchar2(32) DEFAULT ON NULL SYS_GUID(),
map_student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$course_flex JSON (OBJECT),
ora$student_flex JSON (OBJECT),
PRIMARY KEY(map_course_id,map_student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(32) /* UNIQUE */,
salary number /* UNIQUE */,
department varchar2(32),
ora$course_flex JSON (OBJECT),
ora$teacher_flex JSON (OBJECT),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(32) /* UNIQUE */,
course_id varchar2(32) DEFAULT ON NULL SYS_GUID(),
class_type varchar2(32),
credit_hours number,
"_id_teacher_root" number,
ora$teacher_flex JSON (OBJECT),
ora$course_flex JSON (OBJECT),
PRIMARY KEY(course_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age number,
name varchar2(32) /* UNIQUE */,
student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$course_flex JSON (OBJECT),
ora$student_flex JSON (OBJECT),
PRIMARY KEY(student_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)';
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)';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root
FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id")';
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_course_root_to_teacher_root_index
ON course_root("_id_teacher_root")';
END;
/For each duality view <view-name>, each
table that directly underlies the top-level fields of an object in the supported
documents has a flex column named
ora$<view-name>_flex (because
useFlexFields was implicitly true for the DDL
generation).
Tables student_root and teacher_root
have primary-key columns student_id and _id,
respectively.
Table course_root has primary-key column
course_id. Its column _id_teacher_root is a
foreign key to column _id of table teacher_root,
which is the primary key of that table. Table course_root has an
index on its foreign-key column, _id_teacher_root.
Table map_course_root_to_student_root is a mapping
table between tables course_root and student_root.
-
Its primary key is a composite of its columns
map_course_idandmap_student_id. -
Its columns
map_course_idandmap_student_idare foreign keys to columnscourse_idandstudent_idin tablescourse_rootandstudent_root, respectively, which are the primary-key columns of those tables. -
It has indexes on its two foreign-key columns.
Example 8-11 DDL Generated For Duality Views (useFlexFields:true)
This is the DDL code for the duality views. It is generated using
useFlexFields:true. The duality-view definitions here use
GraphQL syntax. Equivalent SQL duality-view definitions are shown in Example 8-12. The DDL that defines the underlying tables is shown in Example 8-10.
BEGIN
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
{
ora$mapCourseId: course_id
ora$mapStudentId: student_id
ora$student_flex @flex
course_root @unnest @update
{
name
courseNumber: course_id
}
}
studentId @generated (path: "$._id")
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
salary
department
coursesTaught: course_root @insert @update @delete
{
name
courseId: course_id
classType: class_type
ora$teacher_flex @flex
}
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
teacher: teacher_root @update
{
name
teacherId: "_id"
ora$course_flex @flex
}
courseId @generated (path: "$._id")
students: map_course_root_to_student_root @insert @update @delete
{
ora$mapCourseId: course_id
ora$mapStudentId: student_id
ora$course_flex @flex
student_root @unnest @update
{
name
studentId: student_id
}
}
creditHours: credit_hours
ora$course_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;
/Views course and student each have a
field (courseId and studentId, respectively) whose
value is not stored but is generated from the value of the view's field
_id.
Views course and student each have a
before-insert trigger (insert_trigger_course and
insert_trigger_student, respectively) that stores the value of
an incoming field courseId or studentId,
respectively, in field _id.
Why? A duality view must have an _id field, which
corresponds to the primary-key column(s) of the root table that underlies it, but
documents from the existing app instead have a courseId or
studentId. In views course and
student those fields are always generated from field
_id, so inserting a document stores their values in field
_id instead. (See Document-Identifier Field for Duality Views.)
Example 8-12 SQL DDL Code For Duality-View Creations (useFlexFields:true)
For information, in case SQL is more familiar to you than GraphQL, this SQL DDL code is equivalent to the GraphQL duality-view creation code shown in Example 8-10.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
SELECT JSON {'_id' : s.student_id,
'age' : s.age,
'name' : s.name,
'courses' :
[SELECT JSON {'ora$mapCourseId' : m.map_course_id,
'ora$mapStudentId' : m.map_student_id,
m.ora$course_flex AS FLEX,
UNNEST
(SELECT JSON {'name' : c.name,
'courseNumber' : c.course_id}
FROM course_root c WITH UPDATE
WHERE c.course_id = m.map_course_id)}
FROM map_course_root_to_student_root m WITH INSERT UPDATE DELETE
WHERE s.student_id = m.map_student_id],
'studentId' IS GENERATED USING PATH '$._id',
s.ora$student_flex AS FLEX
RETURNING JSON}
FROM student_root s WITH INSERT UPDATE DELETE;
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
SELECT JSON {'_id' : t."_id",
'name' : t.name,
'salary' : t.salary,
'department' : t.department,
'coursesTaught' :
[SELECT JSON {'name' : c.name,
'courseId' : c.course_id,
'classType' : c.class_type,
c.ora$course_flex AS FLEX}
FROM course_root c WITH INSERT UPDATE DELETE
WHERE c."_id_teacher_root" = t."_id"],
t.ora$teacher_flex AS FLEX
RETURNING JSON}
FROM teacher_root t WITH INSERT UPDATE DELETE;
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
SELECT JSON {'_id' : c.course_id,
'name' : c.name,
'teacher' : (SELECT JSON {'name' : t.name,
'teacherId' : t."_id",
t.ora$teacher_flex AS FLEX}
FROM teacher_root t WITH UPDATE
WHERE t."_id" = c."_id_teacher_root"),
'courseId' IS GENERATED USING PATH '$._id',
'students' :
[SELECT JSON {'ora$mapCourseId' : m.map_course_id,
'ora$mapStudentId' : m.map_student_id,
m.ora$student_flex AS FLEX,
UNNEST
(SELECT JSON {'name' : s.name,
'studentId' : s.student_id}
FROM student_root s WITH UPDATE
WHERE s.student_id = m.map_student_id)}
FROM map_course_root_to_student_root m WITH INSERT UPDATE DELETE
WHERE c.course_id = m.map_course_id],
'creditHours' : c.credit_hours,
c.ora$course_flex AS FLEX
RETURNING JSON
}
FROM course_root c WITH INSERT UPDATE DELETE;
Example 8-13 Create a JSON Schema for the Course Duality View
CREATE TABLE course_schema AS
SELECT DBMS_JSON_SCHEMA.describe('COURSE') AS data FROM DUAL;
SELECT json_serialize(data PRETTY ORDERED) FROM course_schema;(Some insignificant whitespace is removed from the JSON data shown here, to facilitate readability.)
{"additionalProperties" : true,
"dbObject" : "JANUS.COURSE",
"dbObjectType" : "dualityView",
"title" : "COURSE",
"type" : "object",
"properties" :
{"_id" :
{"extendedType" : "string",
"dbAssign" : true,
"maxLength" : 32,
"dbFieldProperties" : [ "check" ]},
"_metadata" : {"asof" : {"extendedType" : "string",
"maxLength" : 20},
"etag" : {"extendedType" : "string",
"maxLength" : 200}},
"courseId" : {"dbFieldProperties" : [ "computed" ]},
"creditHours" : {"dbFieldProperties" : [ "update", "check" ],
"extendedType" : [ "number", "null" ]},
"name" : {"maxLength" : 32,
"dbFieldProperties" : [ "update", "check" ],
"extendedType" : [ "string", "null" ]},
"students" :
{"type" : "array",
"items" :
{"additionalProperties" : false,
"type" : "object",
"properties" :
{"name" : {"maxLength" : 32,
"dbFieldProperties" : [ "update", "check" ],
"extendedType" : [ "string", "null" ]},
"ora$mapCourseId" : {"extendedType" : "string",
"dbAssign" : true,
"maxLength" : 32,
"dbFieldProperties" : [ "check" ]},
"ora$mapStudentId" : {"extendedType" : "number",
"dbAssign" : true,
"dbFieldProperties" : [ "check" ]},
"studentId" : {"extendedType" : "number",
"dbAssign" : true,
"dbFieldProperties" : [ "check" ]},
"dbPrimaryKey" : [ "ora$mapCourseId", "ora$mapStudentId" ]},
"required" : [ "ora$mapCourseId",
"ora$mapStudentId",
"studentId" ]}},
"teacher" : {"additionalProperties" : true,
"type" : "object",
"properties" :
{"name" : {"maxLength" : 32,
"dbFieldProperties" : [ "update",
"check" ],
"extendedType" : [ "string",
"null" ]},
"teacherId" :
{"extendedType" : "number",
"dbAssign" : true,
"dbFieldProperties" : [ "check" ]},
"dbPrimaryKey" : [ "teacherId" ]},
"required" : [ "teacherId" ]},
"dbPrimaryKey" : [ "_id" ]},
"dbObjectProperties" : [ "insert", "update", "delete", "check" ],
"required" : [ "_id" ]}Any field not listed in the value
of field required is optional; it need not be present in a
valid document supported by the duality view.
Field
additionalProperties is a partner to its sibling
field properties. If additionalProperties is
absent or is true, then documents supported by the view can contain
additional fields that are siblings of the fields listed in field
properties. Such additional fields are stored in a flex column;
they are, in effect, not explicitly specified in the duality-view definition.
For each flex column in a duality-view definition there is a
true
additionalProperties field (implicit if the field is absent) in its
JSON schema, and vice versa.
The JSON Schema fields with
prefix db are Oracle-specific.
-
Field
dbFieldPropertiesis an array that specifies properties for a particular field. These include annotations, such as"update"and"check", as well as"computed"for a field whose value is generated, not stored. For example, fieldcourseIdis not stored but is taken from the value of field_id(which is taken from columncourse_id. -
Field
dbAssignistruefor a field, such as_id,ora$mapCourseId, andora$mapStudentId, that is not present in the original document set. These three fields are present in the course documents because their values are the primary keys for underlying tables. -
Field
dbPrimaryKeydeclares that fields_id,ora$mapCourseId,ora$mapStudentId, andteacherIdare the primary-key fields.
Fields type and
extendedType are important for comparing a
duality-view JSON schema with the schema for the corresponding input (transfer)
table. The types should generally correspond. When these fields have an array value
it means that the type can be any of the types listed in the array.
Note:
When the type of a field includes "null", either
(1) the field value can be a JSON null value or (2) the field
can be absent. This is because a JSON null value can correspond
to a SQL NULL value, which indicates absence of a value.
Related Topics
See Also:
DESCRIBE Function in Oracle Database PL/SQL
Packages and Types Reference for information about function
DBMS_JSON_SCHEMA.describe
Parent topic: JSON-To-Duality Converter
8.2.4 Using the Converter with useFlexFields:false
If you use the converter with configuration field
useFlexFields set to false then, during import, an
error is logged for a field that can't be stored in a simple SQL scalar column, instead of the
field being stored in a flex column.
Example 8-22, Example 8-23, and Example 8-24 illustrate this.
Example 8-8 shows that fields Notes and creditHours are
outliers for the document set, Notes because it is rare, and
creditHours because its value is sometimes of a rare type.
With useFlexFields
true (the default value) field Notes is retained in course
documents, by being stored in a flex field. With useFlexFields
false, however, the rare field is logged as an error during import.
Example 8-14 Infer Database Objects and Generate Their DDL (Configured Without Flex Columns)
This example is the same as Example 8-9, except that useFlexFields is false and
outputFormat is standalone.
DECLARE
ddl_sql CLOB;
BEGIN
ddl_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON('{"tableNames" : [ "STUDENT_TAB", "COURSE_TAB", "TEACHER_TAB" ],
"viewNames" : [ "STUDENT", "COURSE", "TEACHER" ],
"useFlexFields" : false,
"outputFormat" : "standalone",
"minFrequency" : 25}'));
DBMS_OUTPUT.put_line('DDL Script: ');
DBMS_OUTPUT.put_line(ddl_sql);
END;
/
The resulting DDL is shown in Example 8-15and Example 8-16.
See Also:
INFER_AND_GENERATE_SCHEMA Function in
Oracle Database PL/SQL
Packages and Types Reference for information about function
DBMS_JSON_DUALITY.infer_and_generate_schema
Example 8-15 DDL Generated For Tables (useFlexFields:false)
This is the DDL code (generated using configuration field
useFlexFields:false) that creates the tables underlying the
duality views. It also creates foreign-key constraints and indexes. It is the same
as that shown in Example 8-10, except that there are no flex columns and the code is standalone (not
wrapped with EXECUTE IMMEDIATE). The DDL that defines the views is
shown in Example 8-16.
CREATE TABLE map_course_root_to_student_root(
map_course_id varchar2(32) DEFAULT ON NULL SYS_GUID(),
map_student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
PRIMARY KEY(map_course_id,map_student_id)
);
CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(32) /* UNIQUE */,
salary number /* UNIQUE */,
department varchar2(32),
PRIMARY KEY("_id")
);
CREATE TABLE course_root(
name varchar2(32) /* UNIQUE */,
course_id varchar2(32) DEFAULT ON NULL SYS_GUID(),
class_type varchar2(32),
credit_hours number,
"_id_teacher_root" number,
PRIMARY KEY(course_id)
);
CREATE TABLE student_root(
age number,
name varchar2(32) /* UNIQUE */,
student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
PRIMARY KEY(student_id)
);
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);
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);
ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root
FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id");
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);
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);
CREATE INDEX IF NOT EXISTS
fk_course_root_to_teacher_root_index
ON course_root("_id_teacher_root");
Example 8-16 DDL Generated For Duality Views (useFlexFields:false)
This is the DDL code for the duality views. It is generated using
useFlexFields:false. It is the same as that shown in Example 8-11, except that there are no flex columns and the code is standalone (not
wrapped with EXECUTE IMMEDIATE). The DDL that defines the
underlying tables is shown in Example 8-15.
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
{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
course_root @unnest @update
{
name
courseNumber: course_id
}
}
studentId @generated (path: "$._id")
};
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
salary
department
coursesTaught: course_root @insert @update @delete
{
name
courseId: course_id
classType: class_type
}
};
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
teacher: teacher_root @update
{
name
teacherId: "_id"
}
courseId @generated (path: "$._id")
students: map_course_root_to_student_root @insert @update @delete
{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
student_root @unnest @update
{
name
studentId: student_id
}
}
creditHours: credit_hours
};
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;
/
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;
/
Parent topic: JSON-To-Duality Converter
8.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.
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 8-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 8-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');- 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 withuseFlexFields:true) is shown. All documents are successfully imported, with all of their fields. - Using the Importer, from useFlexFields:false Conversion
After trying to import, error-log tables are queried to show import errors and imported documents.
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: From JSON To Duality
8.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 8-19 Student Document Set (Migrator Output, useFlexFields:true)
Compare this with the input student document set, Example 8-1, which had no outliers. These are the only differences (ignoring field order, which is irrelevant):
-
Document identifier field
_idand document-state field_metadatahave been added. (Every document supported by a duality view has these fields.) -
Fields
ora$mapCourseIdandora$mapStudentIdhave been added. These correspond to the primary-key columns for underlying mapping tablemapping_table_course_root_to_student_root. Their values are the same as the values of fieldscourseNumberandstudentId, 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 8-20 Teacher Document Set (Migrator Output, useFlexFields:true)
Compare this with the input teacher document set, Example 8-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 8-21 Course Document Set (Migrator Output, useFlexFields:true)
Compare this with the input course document set, Example 8-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
8.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 8-17 for the creation of the error-log tables used here, and Example 8-18 for the use of DBMS_JSON_DUALITY.import to import the
document sets into the duality views.
Example 8-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.
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 selectedExample 8-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 selectedExample 8-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: The migrator doesn't help with the third main use case of duality views: Reusing existing relational data (tables) for use in JSON documents.Footnote 2: Mixed-type fields that are outliers because their values are only rarely of a different type than usual are not stored in a flex column. Instead, import tries to convert the rare-type occurrences to the common type for the field.
Footnote 3:
JANUS is the
database schema that owns the tables and views used in these examples.Footnote 4:
JANUS is the database schema that owns the tables and
views used in these examples.Footnote 5:
JANUS
is the database schema that owns the tables and views used in these examples.