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

9.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: Do infer_schema and generate_schema together.

    • 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 minFrequency percent of the documents.

    For example, in the input course documents:

    • Field Notes is an outlier because it occurs in less than minFrequency percent of the documents.

    • Field creditHours is an outlier because it has a string value in less than minFrequency percent of the documents. (It has a number value in the other documents.)

    The default minFrequency value is 5, 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 25 as the minFrequency value, 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/SQL EXECUTE 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 use EXECUTE 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 a JSON-type column (it need not be named data), 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). When true, annotations are set for maximum updatability of each view. When false all 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 useFlexFields is true, for each duality view <view-name>, a flex column named ora$<view-name>_flex is 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 useFlexFields is true the 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 1 When useFlexFields is false such 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 tableNames with _duality appended are used as the view names. For example the name of the view corresponding to the documents in table foo defaults to foo_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 _id is generated for each document, if it is not already present in the input documents.

    A duality view must have a top-level _id field (the document identifier), which corresponds to the identifying column(s) of the view's root table (primary-key columns or columns with a unique constraint or unique index). If a document input to the converter already has a top-level _id field, then its associated column is in the root table and is chosen as the table's identifying column.

  • Document-handling field _metadata is 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 identifying 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 identifying columns, 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 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.
  • 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.
  • 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.
  • 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.

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: Migrating From JSON To Duality

9.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 9-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 9-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 9-5 and Example 9-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 9-6 and the resulting data guides, Example 9-7 and Example 9-8.

Example 9-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 9-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 9-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 2 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 9-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 9-7 and Example 9-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 9-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 9-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:

Parent topic: JSON-To-Duality Converter

9.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 useFlexFields is true (the default) then fields for which there is no corresponding column will be stored in a flex column. If it is false they'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 9-5) with the schema for duality view course (Example 9-13).

    If useFlexFields is true then the two schemas should be functionally equivalent. If it is false then 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:frequency value for each missing field, to see whether or not the field is an outlier. Check also the type value 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

9.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 9-13. Comparing that with the JSON schema for the input course document set, Example 9-5 shows that the document fields correspond, with the exception of the two outlier fields Notes and creditHours:

  • Field Notes is missing from the documents supported by the duality view. This is because it occurs in less than minFrequency (25) percent of the documents.

  • Field creditHours is not missing, but its type has changed from number or string to just number. This is because (1) a string value is present in less than minFrequency (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 9-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 field useFlexFields is (by default) true.

  • Field ingestLimit, which means that its value is 100000 (default), so each document set can have no more than 100,000 documents.

  • Field outputFormat, which means that its value is executable (default), so the DDL script can be executed directly using PL/SQL EXECUTE IMMEDIATE.

  • sourceSchema, which means that the views are to be owned by the user (database schema) that is logged in when infer_and_generate_schema is 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 is true (default), so the views are created with maximum updatability.

  • Field useFlexFields, which means that its value is true (default), so (1) fields that are outliers because they aren't present in at least minFrequency percent of the documents of a given type, and (2) non-outlier fields that are of mixed type but with each type used in at least minFrequency percent 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 9-10 and Example 9-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 9-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 9-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_id and map_student_id.

  • Its columns map_course_id and map_student_id are foreign keys to columns course_id and student_id in tables course_root and student_root, respectively, which are the primary-key columns of those tables.

  • It has indexes on its two foreign-key columns.

Example 9-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 9-12. The DDL that defines the underlying tables is shown in Example 9-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 identifying columns of the root table that underlies it, but documents from the existing app instead have a courseId or studentId field. 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 9-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 9-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 9-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.)Foot 3

{"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 dbFieldProperties is 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, field courseId is not stored but is taken from the value of field _id (which is taken from column course_id.

  • Field dbAssign is true for a field, such as _id, ora$mapCourseId, and ora$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 dbPrimaryKey declares that fields _id, ora$mapCourseId, ora$mapStudentId, and teacherId correspond to identifying columns.

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

9.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 9-22, Example 9-23, and Example 9-24 illustrate this.

Example 9-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 9-14 Infer Database Objects and Generate Their DDL (Configured Without Flex Columns)

This example is the same as Example 9-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 9-15and Example 9-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 9-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 9-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 9-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 9-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 9-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 9-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


Footnote Legend

Footnote 1: 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 2: JANUS is the database schema that owns the tables and views used in these examples.
Footnote 3: JANUS is the database schema that owns the tables and views used in these examples.