8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Migrating Text-Based JSON to JSON Data Type : Pre-Migration Check in Oracle Database 23c

In Oracle 23c the JSON_TYPE_CONVERTIBLE_CHECK procedure has been added to the DBMS_JSON package to allow pre-migration checks to be performed on text-based JSON columns.

The Problem
The Solution : DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK
Migrate the Data to a JSON Column

The Problem

Prior to Oracle 21c, all JSON data was stored in the database as text, typically in columns with data types such as BLOB, CLOB or VARCHAR2. With the introduction of the JSON data type in Oracle 21c, people may wish to convert their text-based JSON columns to use the JSON data type. There are several methods to do this, including the following.

CREATE TABLE ... AS SELECT (CTAS)
Data Pump
Online Table Redefinition
Add the new column and perform a DML update

In all cases we don't know if the data in the column is suitable for such a conversion until we attempt it.

The Solution : DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK

In Oracle 23c the JSON_TYPE_CONVERTIBLE_CHECK procedure has been added to the DBMS_JSON package.

PROCEDURE JSON_TYPE_CONVERTIBLE_CHECK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 TABLENAME                      VARCHAR2                IN
 COLUMNNAME                     VARCHAR2                IN
 STATUSTABLENAME                VARCHAR2                IN
 FASTCHECK                      BOOLEAN                 IN     DEFAULT
 APPENDSTATUS                   BOOLEAN                 IN     DEFAULT

This procedure allows us to perform a pre-migration check on the contents of the text-based JSON column, to make sure it is suitable for conversion to a JSON data type column.

We create test table with a CLOB column to hold JSON data.

drop table if exists json_data_precheck purge;
drop table if exists json_data purge;

create table json_data (
  id    number generated always as identity,
  data  clob
);

We populate it with three rows that are suitable for conversion to a JSON data type column.

insert into json_data (data)
values (null);

insert into json_data (data)
values ('{}');

insert into json_data (data)
values ('{"product":"banana", "quantity":10}');
commit;

We run the JSON_TYPE_CONVERTIBLE_CHECK procedure, passing in the column of interest, and a status table name. This table will be created.

begin
  dbms_json.json_type_convertible_check(
    owner           => 'testuser1',
    tablename       => 'json_data',
    columnname      => 'data',
    statustablename => 'json_data_precheck'
  );
end;
/

We can see the JSON_DATA_PRECHECK table has been created.

desc json_data_precheck
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STAMP                                              TIMESTAMP(6)
 SCHEMA_NAME                                        VARCHAR2(130)
 TABLE_NAME                                         VARCHAR2(130)
 COLUMN_NAME                                        VARCHAR2(130)
 ERROR_ROW_ID                                       ROWID
 ERROR_CODE                                         VARCHAR2(250)
 STATUS                                             VARCHAR2(100)

SQL>

We check the contents of the JSON_DATA_PRECHECK table and see that a conversion is possible because no errors were found.

set linesize 200
column stamp format A30
column schema_name format A12
column table_name format A12
column column_name format A12
column error_row_id format A20
column error_code format A20
column status format A40

select * from json_data_precheck;


STAMP                          SCHEMA_NAME  TABLE_NAME   COLUMN_NAME  ERROR_ROW_ID         ERROR_CODE           STATUS
------------------------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
13-APR-23 10.04.15.632861 AM   TESTUSER1    JSON_DATA    DATA                                                   Process completed (Errors found: 0)

SQL>

We insert a row into the test table that is not valid JSON.

insert into json_data (data)
values ('banana');
commit;

We run the pre-migration check again.

drop table if exists json_data_precheck purge;

begin
  dbms_json.json_type_convertible_check(
    owner           => 'testuser1',
    tablename       => 'json_data',
    columnname      => 'data',
    statustablename => 'json_data_precheck'
  );
end;
/

We check the contents of the JSON_DATA_PRECHECK table and see that a conversion is not possible because we have some errors. Notice an entry is included for each failing row.

select * from json_data_precheck;

STAMP                          SCHEMA_NAME  TABLE_NAME   COLUMN_NAME  ERROR_ROW_ID         ERROR_CODE           STATUS
------------------------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
13-APR-23 10.07.40.764675 AM   TESTUSER1    JSON_DATA    DATA         AAAU2UAAPAAAAG3AAD   JSON SYNTAX ERROR    ERROR FOUND
13-APR-23 10.07.40.776563 AM   TESTUSER1    JSON_DATA    DATA                                                   Process completed (Errors found: 1)

SQL>

We can check that data and decide what to do about it.

select * from json_data where rowid = 'AAAU2UAAPAAAAG3AAD';

        ID DATA
---------- --------------------------------------------------------------------------------
         4 banana

SQL>

Let's "fix" the row.

update json_data
set    data = '{"product":"banana", "quantity":1}'
where  id = 4;
commit;

We run the pre-migration check again, and see the conversion is possible again.

drop table if exists json_data_precheck purge;

begin
  dbms_json.json_type_convertible_check(
    owner           => 'testuser1',
    tablename       => 'json_data',
    columnname      => 'data',
    statustablename => 'json_data_precheck'
  );
end;
/


select * from json_data_precheck;

STAMP                          SCHEMA_NAME  TABLE_NAME   COLUMN_NAME  ERROR_ROW_ID         ERROR_CODE           STATUS
------------------------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
13-APR-23 10.12.02.719336 AM   TESTUSER1    JSON_DATA    DATA                                                   Process completed (Errors found: 0)

SQL>

Migrate the Data to a JSON Column

We can now move forward and convert the data using one of the valid methods.

-- Add a JSON column,
alter table json_data add (
  data2 json
);

-- Populate the new column.
update json_data
set    data2 = JSON(data);

-- Drop the old column. You may prefer to mark it as unused.
alter table json_data drop column data;

-- Rename the new column to match the original name.
alter table json_data rename column data2 to data;

We can see the data type of the column is now JSON, and it contains the converted data.

desc json_data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DATA                                               JSON

SQL>

column data format a40

select * from json_data;

        ID DATA
---------- ----------------------------------------
         1
         2 {}
         3 {"product":"banana","quantity":10}
         4 {"product":"banana","quantity":1}

SQL>

For more information see:

DBMS_JSON
Migrating Textual JSON Data to JSON Data Type
Performing a Pre-Migration Check
JSON Support Enhancements in Oracle Database 23c
JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles

Hope this helps. Regards Tim...

Back to the Top.