8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.
Related articles.
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
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...