DBA Hub

📋Steps in this guide1/3

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

In Oracle 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
13 views
1

The Problem

Prior to Oracle 21c, all JSON data was stored in the database as text, typically in columns with data types such as , or . 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.
2

The Solution : DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK

In Oracle 23ai/26ai the procedure has been added to the package. 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 column to hold JSON data. We populate it with three rows that are suitable for conversion to a JSON data type column. We run the procedure, passing in the column of interest, and a status table name. This table will be created. We can see the table has been created. We check the contents of the table and see that a conversion is possible because no errors were found. We insert a row into the test table that is not valid JSON. We run the pre-migration check again. We check the contents of the table and see that a conversion is not possible because we have some errors. Notice an entry is included for each failing row. We can check that data and decide what to do about it. Let's "fix" the row. We run the pre-migration check again, and see the conversion is possible again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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

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
);

insert into json_data (data)
values (null);

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

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

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

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>

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>

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

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.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>

select * from json_data where rowid = 'AAAU2UAAPAAAAG3AAD';

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

SQL>

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

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>
3

Migrate the Data to a JSON Column

We can now move forward and convert the data using one of the valid methods. We can see the data type of the column is now JSON, and it contains the converted data. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 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;

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!