21.8 JSON-To-Duality Converter: What It Does

The converter infers the inherent structure and typing of one or more sets of stored documents, as a JSON schema. Using the schema, the converter generates DDL code to create the database objects needed to support the document sets: duality views and their underlying tables and indexes.

The JSON schema inferred from the input document sets includes a relational schema that represents the relations (tables, columns, and key constraints) that implicitly underlie the data in the JSON documents.

The generated DDL 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.

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, identity 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 columns are in the root table and are chosen as the table's identifying columns.

• 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, map_table_course_root_to_student_root, which has two identifying columns, map_course_id and map_student_id. These have foreign-key references to the identifying 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 identifying 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 "DDL Code from GENERATE_SCHEMA with useFlexFields=true" in Using the Converter, Default Behavior. (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.)

For descriptions of the PL/SQL subprograms comprising the converter, see:

About Migrations from JSON to Duality.