DBA Hub

📋Steps in this guide1/6

JSON Type Modifier Enhancements in Oracle Database 23ai/26ai

Oracle database 23ai/26ai (23.8) allows greater control over the contents of JSON columns stored in the database using JSON type modifiers.

oracle 23configurationintermediate
by OracleDba
19 views
1

Default Action

We create a new table containing a column of type JSON with no modifier. This is what we might expect to see in a previous release. This is the most flexible option, as there is no limitation on what type of JSON it can store, or what size the JSON can be. The column in the or view shows us the current lack of a modifier. Without a JSON type modifier we can store a variety of valid JSON, or NULL values if there is not a constraint on the column.

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
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json
);


column table_name format a20
column column_name format a20
column json_modifier format a30

select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( )

SQL>

insert into t1 (data)
values (null),
       ('{}'),
       ('{"fruit":"apple"}'),
       ('[{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"},{"fruit":"pear"}]'),
       ('[5,4,3,2,1]');


set linesize 100
column data format a80

select id, data
from   t1
order by id;

        ID DATA
---------- --------------------------------------------------------------------------------
         1
         2 {}
         3 {"fruit":"apple"}
         4 [{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"},{"fruit":"pear"}]
         5 [5,4,3,2,1]

SQL>
2

Limit JSON Size

We use the clause to specify the maximum size in bytes of the JSON stored in a JSON column. Remember, this is bytes, not characters. We are still able to insert any valid JSON, so long as it is smaller than 50 bytes. If we exceed the size limit, we get an error. Interestingly, the limit is not displayed in the column of the view.

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
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json (limit 50)
);

insert into t1 (data)
values ('{}'),
       ('{"fruit":"apple"}'),
       ('[5,4,3,2,1]');

insert into t1 (data)
values ('[{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"},{"fruit":"pear"}]');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42712: JSON binary encoding exceeds JSON binary size limit 50 specified in JSON type modifier.
Help: https://docs.oracle.com/error-help/db/ora-42712/

SQL>

column table_name format a20
column column_name format a20
column json_modifier format a30

select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( )

SQL>
3

JSON Object Modifier

We can limit the type of JSON stored in the JSON column using a JSON type modifier. In the following example we limit the JSON to holding valid JSON objects using the modifier. We can store valid JSON objects (surrounded by { }), but if we try to store a valid JSON array (surrounded by [ ]), we get an error.

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
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(object)
);


column table_name format a20
column column_name format a20
column json_modifier format a30

select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( OBJECT )

SQL>

insert into t1 (data)
values ('{}'),
       ('{"fruit":"apple"}');

2 rows created.

SQL>


insert into t1 (data)
values ('[{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"},{"fruit":"pear"}]');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('ARRAY') instance does not match 'JSON( OBJECT )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>
4

JSON Array Modifier

The modifier allows us to force the JSON contents to be a valid JSON array. Now we can insert valid JSON arrays, but a valid JSON object will fail to insert. We have a lot of control over the contents of the JSON arrays. In the following example we limit the array to containing only numbers, with a maximum of 5 array elements and sort the contents of the array. We can use '*' to indicated unlimited array entries. We insert a valid JSON array and it sorts the elements for us. We get an error if we insert too many elements in the array, or if we attempt to insert an array not containing numbers. We can explicitly control the presence of nulls in the array using or . By default we are using . In the following example we allow nulls. As expected, we can store a null value in the array. Here are some variations that should make things clear.

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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array)
);


column table_name format a20
column column_name format a20
column json_modifier format a30
               
select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( ARRAY )

SQL>

insert into t1 (data)
values ('[{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"},{"fruit":"pear"}]'),
       ('[5,4,3,2,1]');

2 rows created.

SQL>


insert into t1 (data)
values ('{"fruit":"apple"}');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('OBJECT') instance does not match 'JSON( ARRAY )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>

drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number, 5, sort))
);


column table_name format a20
column column_name format a20
column json_modifier format a30
               
select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( ARRAY, NUMBER )

SQL>

insert into t1 (data)
values ('[5,4,3,2,1]');


select data from t1;

DATA
--------------------------------------------------
[1,2,3,4,5]

SQL>

insert into t1 (data)
values ('[6,5,4,3,2,1]');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 3, SORT) )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>


insert into t1 (data)
values ('[{"fruit":"apple"},{"fruit":"banana"},{"fruit":"orangle"}]');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 3, SORT) )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>

insert into t1 (data)
values ('[null,4,3,2,1]');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('') instance does not match 'JSON( ARRAY (NUMBER, 5, SORT) )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>

drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number allow null, 5, sort))
);


column table_name format a20
column column_name format a20
column json_modifier format a30
               
select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( ARRAY, NUMBER, NULL )

SQL>

insert into t1 (data)
values ('[null,4,3,2,1]');

select data from t1;

DATA
--------------------------------------------------
[null,1,2,3,4]

SQL>

-- Array of numbers. Implied disallow null, unlimited array size and no sort.
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number))
);


-- Array of numbers limited to 5 elements. Implied disallow null and no sort.
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number, 5))
);


-- Array of numbers limited to 5 sorted elements. Implied disallow null.
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number, 5, sort))
);


-- Array of numbers with unlimited elements and sorted. Implied disallow null.
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number, *, sort))
);


-- Array of numbers limited to 5 elements and sorted. Null values are allowed.
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(array (number allow null, 5, sort))
);
5

JSON Scalar Modifier

The modifier allows us to limit a JSON column to storing scalar values. We can now insert a scalar number, but any other data type will fail. Supported scalars include the following. - BOOLEAN - BINARY - BINARY_DOUBLE - BINARY_FLOAT - DATE - INTERVAL DAY TO SECOND - INTERVAL YEAR TO MONTH - NULL - NUMBER - STRING - TIMESTAMP - TIMESTAMP WITH TIME ZONE

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
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(scalar number)
);


column table_name format a20
column column_name format a20
column json_modifier format a30
               
select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( NUMBER )

SQL>

insert into t1 (data)
values ('1234556780');

select data from t1;

DATA
--------------------------------------------------------------------------------
1234556780

SQL>


insert into t1 (data)
values ('{"fruit":"apple"}');

insert into t1 (data)
            *
ERROR at line 1:
ORA-42700: jsontype ('OBJECT') instance does not match 'JSON( SCALAR NUMBER )' type modifier
Help: https://docs.oracle.com/error-help/db/ora-42700/

SQL>
6

Combining Modifiers

Multiple modifiers can be combined for a single column. In the following example we allow JSON objects and scalar dates to be stored in the column. 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
drop table if exists t1 purge;

create table t1 (
  id number generated as identity primary key,
  data json(object, scalar date)
);


column table_name format a20
column column_name format a20
column json_modifier format a30
               
select table_name,
       column_name,
       json_modifier
from   user_tab_cols
where  json_modifier is not null
order by 1;

TABLE_NAME           COLUMN_NAME          JSON_MODIFIER
-------------------- -------------------- ------------------------------
T1                   DATA                 JSON( OBJECT, DATE )

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!