21.11 Using the Converter for Incremental Schema Inference

Learn how to add new JSON document sets to an existing Duality View schema while reusing compatible tables and relationships.

With incremental schema inference, the migrator analyzes new collections in the context of the existing views and determines which relational structures it can reuse and which structures it must create or extend.

• About Incremental Duality View Schema Inference
  You can use incremental inference when you want to update the existing schema from an earlier migrator run. Specify the existing schema as a list of Duality View names. These views define the underlying base tables, keys, relationships, and constraints that the migrator uses as context.
• How Existing-Schema Reuse and Semantic Equivalence Work
  The migrator process of inferring structure uses both the new collection and the existing collection.
• Mapping JSON Fields to Relational Columns
  Schema inference converts JSON fields into relational columns and relationships. Simple scalar fields generally become columns in a root or child table.
• Generated DDL and Schema Evolution
  The migrator expresses its schema decisions as SQL DDL. It represents new entities with CREATE TABLE statements and changes to existing entities with ALTER TABLE statements.
• Example: Create a Duality View from a New Collection
  In this procedure, we add a LAB Duality View to an existing schema that contains the STUDENT, TEACHER, and COURSE Duality Views.

21.11.1 About Incremental Duality View Schema Inference

You can use incremental inference when you want to update the existing schema from an earlier migrator run. Specify the existing schema as a list of Duality View names. These views define the underlying base tables, keys, relationships, and constraints that the migrator uses as context.

During inference, the migrator compares each table inferred from the new collection with the existing base tables. When it finds an equivalent table, it reuses that table. When necessary, it alters the existing table to add columns, foreign-key constraints, or indexes.

The generated output contains CREATE TABLE and ALTER TABLE statements. It can also contain primary keys, foreign keys, indexes, Duality View definitions, and supporting triggers.

To establish equivalence, the existing Duality Views must contain data that the migrator can use for matching. A schema definition without representative data might not provide enough evidence for reliable matching.

Incremental schema inference uses the existingViewNames parameter. This parameter accepts an array of existing Duality View names. Incremental inference is useful when collections arrive over time, when a data model grows organically, or when multiple JSON views share the same underlying entities. It preserves relationships and reduces unnecessary duplication of base tables.

In the example procedure, the existing schema supports the STUDENT, TEACHER, and COURSE Duality Views. When you add the LAB collection, the migrator creates a new lab_root table, because labs are a new root entity. It also extends student_root and teacher_root, because the lab documents contain information about existing students and teachers. After you complete the procedure, the schema contains a new LAB Duality View that is integrated with the existing relational design.

21.11.2 How Existing-Schema Reuse and Semantic Equivalence Work

The migrator process of inferring structure uses both the new collection and the existing collection.

The migrator does not reuse a table solely because two JSON fields have similar names. It compares the structure inferred from the new collection with the structures represented by the existing Duality Views. It evaluates whether each new nested object or array represents the same logical entity as an existing relational table.

For the LAB collection in our example, each student entry contains a studentId and a name. These fields correspond to the existing student entity. Each convenor entry contains an ID, a name, and an office. The migrator can match these entries to existing teacher entities when the populated views provide sufficient evidence of equivalence. The migrator can then reuse student_root and teacher_root while adding the attributes required by the LAB Duality View. Semantic matching depends on data in the existing views. A schema definition without representative data might not provide enough evidence for reliable matching.

21.11.3 Mapping JSON Fields to Relational Columns

Schema inference converts JSON fields into relational columns and relationships. Simple scalar fields generally become columns in a root or child table.

To illustrates this principle, in the example procedure, the following fields become columns in lab_root:

• labId
• labName
• dept
• location

Nested fields require additional interpretation. The students array in a lab document refers to existing students, so the inferred model adds a relationship from the lab to student_root.

The isSponsored field describes if the candidate is sponsored. In this example, the generated model stores it in student_root.is_sponsored. Similarly, convenors.office becomes teacher_root.office.

The Duality View preserves the JSON field names at its boundary. The relational model uses names such as lab_id, lab_name, and is_sponsored. The view definition translates between these representations.

21.11.4 Generated DDL and Schema Evolution

The migrator expresses its schema decisions as SQL DDL. It represents new entities with CREATE TABLE statements and changes to existing entities with ALTER TABLE statements.

In addition to representing new entities and changes to existing entities, the generated DDL can also include:

• Primary keys
• Foreign-key constraints
• Indexes
• Duality View definitions
• Supporting triggers

In the lab example, the generated schema creates lab_root. It alters student_root to add is_sponsored and a reference to the lab. It alters teacher_root to add office and a reference to the lab. It then creates the relationships and indexes required to connect the new structures.

Review generated DDL before executing it in a production environment. Confirm the following items:

• The migrator reused the expected tables.
• The migrator changes existing columns safely.
• The key types are compatible.
• The generated constraints represent the intended cardinality.
• The generated indexes support the required relationships.

21.11.5 Example: Create a Duality View from a New Collection

In this procedure, we add a LAB Duality View to an existing schema that contains the STUDENT, TEACHER, and COURSE Duality Views.

After you complete this procedure, the migrator will have completed the following steps:

• Create a new lab_root table.
• Reuse and extend student_root and teacher_root.
• Add relationships, foreign keys, and indexes.
• Create the LAB Duality View.
• Import the lab documents.
• Verify the imported data.

Prerequisites:

Confirm the following prerequisites:

• The STUDENT, TEACHER, and COURSE Duality Views exist.
• The lab data is available as valid JSON in lab.json.
• The external directory data_dir is configured.
• You have permission to create and alter the required database objects.

For example:

{
  "labId": "6",
  "labName": "Data and Intelligent Systems Labs",
  "location": "Room No. 32, Main Building",
  "dept": "Computer Science",
  "convenors": [
    {
      "id": 102,
      "name": "Betty Z.",
      "office": "Room No. 02, CSA Building"
    },
    {
      "id": 104,
      "name": "Natalie C.",
      "office": "Room No. 06, CSA Building"
    }
  ],
  "students": [
    {
      "studentId": 5,
      "name": "Hye E.",
      "isSponsored": true
    },
    {
      "studentId": 4,
      "name": "Georgia D.",
      "isSponsored": false
    }
  ]
}

Example 21-23 Step 1: Create the Source and Staging Tables

Create an external table for the lab collection, and then create a local staging table for schema inference.

CONN janus/janus

DROP TABLE IF EXISTS dataset_lab;

CREATE TABLE dataset_lab (
  data JSON
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_BIGDATA
  ACCESS PARAMETERS (
    com.oracle.bigdata.fileformat = jsondoc
  )
  LOCATION (
    data_dir:'lab.json'
  )
)
PARALLEL
REJECT LIMIT UNLIMITED;

DROP TABLE IF EXISTS lab_tab;

CREATE TABLE lab_tab AS
SELECT *
FROM dataset_lab;

Confirm that the source documents are readable:

SELECT json_serialize(data PRETTY)
FROM dataset_lab;

Example 21-24 Step 2: Infer and Generate the Incremental Schema

Run schema inference with LAB_TAB as the source table. Specify LAB as the new view and provide the existing views as context. In this example, we set both frequency thresholds to zero so that all observed fields and types participate in inference.

SET LINESIZE 30000
SET SERVEROUTPUT ON

DECLARE
  er_schema  JSON;
  schema_sql CLOB;
BEGIN
  er_schema :=
    DBMS_JSON_DUALITY.infer_schema(
      JSON('{
        "tableNames"        : ["LAB_TAB"],
        "viewNames"         : ["LAB"],
        "existingViewNames" : ["STUDENT", "TEACHER", "COURSE"],
        "minFieldFrequency" : 0,
        "minTypeFrequency"  : 0
      }')
    );

  schema_sql :=
    DBMS_JSON_DUALITY.generate_schema(er_schema);

  DBMS_OUTPUT.put_line(schema_sql);

  EXECUTE IMMEDIATE schema_sql;
END;
/

Review the generated DDL before executing it in a production environment. Confirm the following items:

• lab_root is created as a new root table.
• student_root and teacher_root are extended as expected.
• The generated keys and foreign keys are correct.
• The LAB Duality View maps the intended JSON fields.
• The generated column types support the source data.

Example 21-25 Step 3: Create an Error Log

Create an error-log table for rows that cannot be imported.

BEGIN
  DBMS_ERRLOG.create_error_log(
    dml_table_name     => 'LAB',
    err_log_table_name => 'LAB_ERR_LOG',
    skip_unsupported   => TRUE
  );
END;
/

Example 21-26 Step 4: Import the Lab Documents

Import the staged lab data through the generated LAB Duality View.

SET SERVEROUTPUT ON

BEGIN
  DBMS_JSON_DUALITY.import_all(
    JSON('{
      "tableNames" : ["LAB_TAB"],
      "viewNames"  : ["LAB"],
      "errorLog"   : ["LAB_ERR_LOG"]
    }')
  );
END;
/

Example 21-27 Step 5: Verify the Duality View

Query the LAB view to confirm that the imported documents contain the expected fields and nested arrays.

SET LINESIZE 80
SET LONG 2000

SELECT json_serialize(data PRETTY)
FROM lab;

Each document should include the following fields:

• labId
• labName
• location
• dept
• students
• convenors
• Generated _id and metadata fields

Example 21-28 Step 6: Verify the Underlying Tables

Confirm that the new lab relationships and collection-specific attributes were written to the existing tables.

SELECT *
FROM teacher_root;

SELECT *
FROM student_root;

Confirm the following changes:

• student_root.is_sponsored contains values from students.isSponsored.
• teacher_root.office contains values from convenors.office.
• student_root.lab_id_lab_root links students to their labs.
• teacher_root.lab_id_lab_root links convenors to their labs.
• lab_root contains the new lab records.

The expected schema changes are as follows:

Object	Expected Change
lab_root	New table for lab-level attributes.
student_root	Adds is_sponsored and a lab reference.
teacher_root	Adds office and a lab reference.
Foreign keys	Link students and teachers to lab_root.
Indexes	Support the new foreign-key relationships.
LAB	New Duality View over the generated schema.
LAB_ERR_LOG	Captures rows that fail during import.

The migrator reuses student_root and teacher_root because populated existing views provide evidence that the nested lab students and convenors represent existing entities. DBMS_JSON_DUALITY.import_all(...) imports the lab JSON documents through LAB.