21.2 About Migrations From JSON To Duality

Learn about the JSON-To-Duality Migrator use cases for existing and new applications.

Including columns of JSON data type in tables that underlie a duality view lets applications add and delete fields, and change the types of field values, in the documents supported by the view. The stored JSON data can be schemaless or JSON Schema-based (to enforce particular types of values).

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.

Migration of existing stored document sets to document collections supported by duality views consists of the following operations. You use the converter for steps 3-8, and the importer for steps 9-13. Steps 1-2 are preliminary. Steps are 2, 4, 7, 10, and 13 are optional.

1. 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.

2. Optionally create JSON data guides that are JSON schemas that describe the input document sets.

   These can be useful for later comparison with parts of the JSON schema inferred by the converter or with the completed document collections resulting from migration.

3. Infer database objects needed to support the input documents: relational tables, indexes, and duality views.

   This step first validates the existing input data, checking whether the document sets can in fact be converted to duality-view support.

   The tables constitute a normalized relational schema. Normalization is both across and within document sets: equivalent data in different document sets is shared, by storing it in the same table.

4. Optionally modify/edit the inferred JSON schema.

5. Create database objects needed to support the input data:

   1. Generate SQL data definition language (DDL) scripts that create the database objects (duality views and their underlying tables and indexes).

   2. Optionally modify/edit the DDL scripts.

   3. Run the scripts to create the database objects.

6. Validate created database objects (the duality views and their underlying relational schema). That is, validate the input documents against the database objects, to see which, if any, documents aren't supported by the duality views, and why.

7. Optionally refine/fix (modify/edit) input documents or DDL scripts.

   1. Optionally modify/edit some input documents that are erroneous (outliers you don't want to keep as is, or conflicting data between different documents), so they fit the created database objects.

   2. Optionally modify/edit the DDL scripts, to change the conversion behavior or the names of the views, tables, or indexes to be created.

8. Repeat steps 3-9, until the document sets to be imported and the (unpopulated) duality views fit together as desired.

9. Import the document sets into the duality views. Check for any errors logged.

10. Optionally refine/fix (modify/edit) input documents or DDL scripts, to fix import errors.

11. Repeat steps 9-10 (or 3-10), as needed, to fix logged import errors, until all documents are successfully imported.

12. Validate import: Check for any problems, with the successfully imported documents.

13. Optionally refine/fix (modify/edit) input documents or DDL scripts to resolve import validation problems.

14. Repeat steps 9-13 (or 3-13), as needed, to fix import problems.

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 the student documentation set, teacher documentation set, and course documentation set in School Administration Example, Migrator Input Documents.

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 (for example, 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. Input 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 migrator 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 that no fields are missing or modified in unacceptable ways.

---

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.