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.

Related Topics