Migrating From JSON To Duality

9 Migrating 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 cases^Foot 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:
1. Generate SQL scripts that create the necessary database objects: duality views and their underlying tables and indexes.
2. Optionally edit the scripts, to change the conversion behavior or the names of the views, tables, and indexes to be created.
3. 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_schema infers the JSON schema that represents all of the input document sets.
generate_schema produces the code to create the required database objects for each duality view.
infer_and_generate_schema performs 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 9-1, Example 9-2, and Example 9-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 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.