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.

_________________________________________________________