D.2 Populate JSON-Type Column By Querying Textual JSON

With this migration approach you populate a new table by querying the original table. You do this as part of the CREATE TABLE statement or an INSERT statement. The new table has the same shape as the original, but with a JSON-type column instead of the textual JSON column.

You submit the query as part of a CREATE TABLE AS SELECT (CTAS) statement or an INSERT as SELECT (IAS) statement. In either case, after populating the new table the old table is dropped (or renamed to some third name), and then the new table is renamed to the original table name.

The two approaches to creating and populating the new table are shown in Example D-2 and Example D-3.

Each of those examples migrates the data in textual-JSON BLOB column po_document of table j_purchaseorder created in Example 15-3. Each uses the JSON data type constructor to create native binary JSON data from the original textual JSON data. Each uses a new table, j_purchaseorder_new, that has the same columns as the original — same names and same data, except that the JSON data is JSON type.

Renaming the new table is shown in Example D-4.

Example D-2 Using CREATE TABLE AS SELECT (CTAS) to Migrate to JSON Data Type

This example created the new table, j_purchaseorder_new, using code similar to that of Example 4-1, but it populates the table as it creates it, with a query of the textual JSON data in the original table.

CREATE TABLE j_purchaseorder_new PARALLEL NOLOGGING AS
SELECT id id, date_loaded date_loaded, JSON(po_document) po_document
  FROM j_purchaseorder;

See Also:

Optimizing Performance by Creating and Populating Tables in Parallel in Oracle Database VLDB and Partitioning Guide for information about using CREATE TABLE AS SELECT to create and populate a table in parallel

Example D-3 Using INSERT as SELECT (IAS) to Migrate to JSON Data Type

This example assumes that you've created the new table, j_purchaseorder_new, using code like that of Example 4-1. It populates the JSON-type column using an INSERT statement that queries the original, textual JSON data.

INSERT /*+ PARALLEL APPEND */
  INTO j_purchaseorder_new (id, date_loaded, po_document)
  SELECT id, date_loaded, JSON(po_document)
    FROM j_purchaseorder;

Optimizer hint PARALLEL requests the optimizer to invoke the INSERT statement in parallel.

Optimizer hint APPEND requests the optimizer to use direct-path insertion, which appends data to the end of the table, rather than using space already allocated to the table. Direct-path insertion can be considerably faster than conventional insertion.

See Also:

• PARALLEL Hint

• APPEND Hint

Example D-4 Rename New Table To Original Table Name

This example renames the new table, which has a JSON-type column, to the name of the original table, which has a textual JSON column.

Before this renaming, it drops the original table. Alternatively, you can rename the original table, if you don't want to drop it immediately. But to be able to rename it you must first drop any existing materialized views that depend on it — see Handling Dependent Objects. You cannot rename a table as long as there are any such dependent materialized views.

Note:

Before dropping the original table, verify the integrity of the new table: make sure it is complete and correct. It needs to have the same column names and data types as the original, except for the type of the JSON column.

DROP TABLE j_purchaseorder;
ALTER TABLE j_purchaseorder_new RENAME TO j_purchaseorder;