D Migrating Textual JSON Data to JSON Data Type

Oracle recommends that you use native binary JSON data (type JSON), rather than textual JSON data (type VARCHAR2, CLOB, or BLOB). How to migrate existing textual JSON data to JSON type is described. This involves (1) a pre-upgrade check, (2) migrating the data, and (3) dealing with dependent database objects.

Using data type JSON avoids costly parsing of textual JSON data and provides better query performance.

You can migrate the data (step 2) in any of the following ways. Each way involves creating a new table of the same shape as the original table, but with a JSON-type column instead of the textual JSON column.

• Populate the JSON-type column using a query of the textual JSON column in the original table. There are two ways to do this: CREATE TABLE AS SELECT (CTAS) or INSERT as SELECT (IAS).

• Use Oracle Data Pump, with data-only mode. Export the original table to a dump file, then import the dump file into the new table.

• Use online redefinition.

Note:

By default, a JSON value returned by a simple dot notation query or a SQL operator (such as json_query) is returned as JSON data type if the input data is JSON type; otherwise it's returned as type VARCHAR2(4000).

Be aware of this difference in default return type if you migrate JSON data stored textually to JSON-type storage. You can override the default return type by specifying RETURNING VARCHAR2(4000) for a SQL operator or using item method string(), to obtain the previous behavior. See RETURNING Clause for SQL Functions and SQL/JSON Path Expression Item Methods.

• Performing a Pre-Migration Check
  You use PL/SQL procedure DBMS_JSON.json_type_convertible_check to check whether a given column of textual JSON data can be converted to JSON data type.
• 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.
• Using Oracle Data Pump to Migrate to JSON Data Type
  With this migration approach you use Oracle Data Pump, in data-only mode, to load the data from the original table that has a textual JSON column into a new table of the same shape, but with a JSON-type column.
• Using Online Redefinition to Migrate to JSON Data Type
  If PL/SQL procedure DBMS_REDEFINITION.can_redef_table gives you the go-ahead, then you can use online redefinition to migrate a textual JSON column to a JSON-type column without significantly affecting the availability of the table data. It remains accessible to both queries and DML during much of the migration process.
• Handling Dependent Objects
  For the JSON-type data that replaces your original, textual JSON data, you need to re-create any database objects that depend on that original data.