Converting Textual JSON to JSON datatype

Oracle database supports JSON data since 12c (12.1.0.2) and also supports JSON natively with all relational database features like transaction, indexing, views, querying etc.
 
Oracle native binary JSON format called OSON is the Oracle extension for JSON format by adding scalar types (date and double), which are not part of the JSON standard. The SQL datatype JSON now uses the format OSON. OSON means, Oracle optimized binary JSON format and it is based on tree encoding,  more details about OSON byte array and navigation offsets are discussed here.
 
Oracle recommends us to use native binary JSON (type JSON), rather than textual JSON (varchar2/clob/blob).
 
If your database has been migrated from an older version (or not using JSON format), then it makes sense to migrate the existing textual JSON data to the JSON type. In this blog post we will see about the various options for migrating textual json to JSON data type.
 
First set up a table, with some sample textual json document in it.
 
demo@FREEPDB1> create table t( id number primary key, info clob );
 
Table created.
 
demo@FREEPDB1>
demo@FREEPDB1> insert into t(id,info)
  2  select rownum , json{* returning clob}
  3  from all_users;
 
51 rows created.
 
The INFO column contains textual json data stored as CLOB data type. Then let’s run the pre-upgrade check using the PL/SQL method JSON_TYPE_CONVERTIBLE_CHECK procedure available in the package DBMS_JSON API. This utility will be querying all the JSON data from the column and validate the OSON conversion on top of it with “error on error” clause. During the analysis if any data is not convertible to json, then a new entry will be added to the status table indicating an error, and the cause for which the data is not convertible. 
 
demo@FREEPDB1> begin
  2     dbms_json.json_type_convertible_check(
  3             owner =>user,
  4             tablename =>'T',
  5             columnname =>'INFO',
  6             statusTableName => 'T_JSON_CHECK');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
Note the newly created table T_JSON_CHECK, query the status column of the newly created table, to confirm that there are no errors.
 
demo@FREEPDB1> select * from T_JSON_CHECK;
 
STAMP                                    SCHEM TABLE COLUM ERROR ERROR STATUS
---------------------------------------- ----- ----- ----- ----- ----- ----------------------------------------
20-FEB-24 01.53.58.255872 PM             DEMO  T     INFO              9% completed (Errors found:0)
20-FEB-24 01.53.58.257008 PM             DEMO  T     INFO              19% completed (Errors found:0)
20-FEB-24 01.53.58.257802 PM             DEMO  T     INFO              29% completed (Errors found:0)
20-FEB-24 01.53.58.258237 PM             DEMO  T     INFO              39% completed (Errors found:0)
20-FEB-24 01.53.58.258906 PM             DEMO  T     INFO              49% completed (Errors found:0)
20-FEB-24 01.53.58.259295 PM             DEMO  T     INFO              58% completed (Errors found:0)
20-FEB-24 01.53.58.259848 PM             DEMO  T     INFO              68% completed (Errors found:0)
20-FEB-24 01.53.58.260233 PM             DEMO  T     INFO              78% completed (Errors found:0)
20-FEB-24 01.53.58.260604 PM             DEMO  T     INFO              88% completed (Errors found:0)
20-FEB-24 01.53.58.261223 PM             DEMO  T     INFO              98% completed (Errors found:0)
20-FEB-24 01.53.58.264058 PM             DEMO  T     INFO              Process completed (Errors found: 0)
 
11 rows selected.
 
Once no errors, we make use of the dbms_redefinition API to perform the data type conversion online.
 
Create Interim table with the required datatype.
 
demo@FREEPDB1> create table t_interim( id number, info json );
 
Table created.
 
Then, check the source table eligible for redefinition and start the redefinition process, with column mapping in place.
 
demo@FREEPDB1> exec dbms_redefinition.can_redef_table(user,'T');
 
PL/SQL procedure successfully completed.
 
demo@FREEPDB1> begin
  2     dbms_redefinition.start_redef_table(user,'T','T_INTERIM',
  3                col_mapping=>'id,json(info) as info' );
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
Then, sync the interim table and finish the redefinition process.
 
demo@FREEPDB1> exec dbms_redefinition.sync_interim_table(user,'T','T_INTERIM');
 
PL/SQL procedure successfully completed.
 
demo@FREEPDB1> exec dbms_redefinition.finish_redef_table(user,'T','T_INTERIM');
 
PL/SQL procedure successfully completed.
 
demo@FREEPDB1> set linesize 71
demo@FREEPDB1> desc t
 Name            Null?    Type
 --------------- -------- ----------
 ID                       NUMBER
 INFO                     JSON
 
Finally, the INFO column that got textual json document was converted to JSON datatype.
 
Next, we will see about converting a binary json to JSON datatype, using a method available in DBMS_JSON package.
 
demo@FREEPDB1> create table t( id number primary key,
  2     info blob check( info is json format oson) );
 
Table created.
 
demo@FREEPDB1> insert into t(id,info)
  2  select rownum , json{* returning blob}
  3  from all_users;
 
51 rows created.
 
demo@FREEPDB1> commit; 

 
Commit complete.

demo@FREEPDB1> set linesize 71
demo@FREEPDB1> desc t
 Name               Null?    Type
 ------------------ -------- --------
 ID                 NOT NULL NUMBER
 INFO                        BLOB
 
Then invoke the blob_to_json method (new utility introduced in 23c to support column with BLOB datatype and IS JSON FORMAT OSON constraint, then such column becomes of JSON type) in DBMS_JSON package, to convert binary json to JSON datatype.
 
demo@FREEPDB1> exec dbms_json.blob_to_json(tablename=>'T',columnName=>'INFO');
 
PL/SQL procedure successfully completed.
 
demo@FREEPDB1> desc t
 Name            Null?    Type
 --------------- -------- --------
 ID              NOT NULL NUMBER
 INFO                     JSON
 
Finally, the INFO column that binary json document, got converted to native JSON datatype.