How to migrate textual JSON data into the new JSON data type

Oracle Database (20c and later) supports JSON natively with all relational database features: transactions, indexing, declarative querying, views, etc.

Oracle’s native binary JSON format called OSON is the Oracle extension of the JSON format by adding scalar types (date and double) which are not part of the JSON standard. The SQL data type JSON uses format OSON.

OSON means Oracle’s optimized binary JSON format and is based on a tree encoding. Here is the OSON encoding of a JSON document as a serialized OSON byte array having tree pointers represented as jump navigation offsets:

Oracle recommends that you use native binary JSON data (type JSON), rather than textual JSON data (type VARCHAR2, CLOB, or BLOB).

If your database has been migrated from an older version or not yet using JSON format, then it makes sense to migrate the existing textual JSON data to the JSON type. This is a 3-step process:

1. Run pre-upgrade check using the PL/SQL procedure DBMS_JSON.json_type_convertible_check
2. Migrate the data (CTAS, DataPump, DBMS_REDEFINITION or add/drop column)
3. Fix the dependent database objects

Let us implement the migration using the 4th method above for the following simple table:

The INFO column contains textual JSON data stored as CLOB data type:

First, let us run the pre-migration check:

Note the newly created table CAR_OWNERS_PRECHECK. Querying the status column of that new table, we can confirm that there are no errors:

Next, we add the new temporary column, update the column data, drop the original column and rename the temp column to its original name:

… gives the same output as in the beginning:

Finally, we can confirm that the data type is JSON:

For the last 3rd step, for all JSON data type data that replaced the original textual JSON data you need to re-create any database objects that depend on that original data.