DBA Hub

📋Steps in this guide1/6

JSON Schema in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai a JSON Schema can validate the structure and contents of JSON documents in your database.

oracle 23configurationintermediate
by OracleDba
32 views
1

What is a JSON Schema?

JSON is extremely flexible, but sometimes we want to validate the structure and contents of our JSON. A JSON Schema is a declarative language that allows us to annotate and validate JSON documents. You can get a full explanation of JSON Schema here . In the examples below we will use the following JSON schema. It will validate the JSON is made up of a JSON object, with two mandatory items, with their minimum and maximum sizes defined. Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data Guide in Oracle 12.2 ( here ), and later made it easier when they enhanced the function in Oracle 18c ( here ). In both cases this the ability to display a JSON Schema, not enforce one.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}
2

VALIDATE Keyword During Table Creation

We use the clause along with the JSON schema when defining a JSON column in our table. We can see the JSON schema is associated with the column by using the view. We create some data to test the JSON schema. Notice that one of the valid examples included the ability to include additional properties that were not in the JSON Schema. This is because JSON is extensible by nature, so JSON Schema only validate minimum requirements by default. We can prevent additional properties being included in the data by setting "additionalProperties" to false, as shown below.

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

create table t1 (
  id         number,
  json_data  json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}',
  constraint t1_pk primary key (id)
);

set long 1000000
column table_name format a10
column column_name format a11
column constraint_name format a15
column json_schema format a40

select table_name,
       column_name,
       constraint_name,
       json_schema
from   user_json_schema_columns;

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME JSON_SCHEMA
---------- ----------- --------------- ----------------------------------------
T1         JSON_DATA   SYS_C0012374    {"type":"object","properties":{"fruit":{
                                       "type":"string","minLength":1,"maxLength
                                       ":10},"quantity":{"type":"number","minim
                                       um":0,"maximum":100}},"required":["fruit
                                       ","quantity"]}

SQL>

-- Valid
insert into t1 (id, json_data) values (1, json('{"fruit":"apple","quantity":10}'));

1 row created.

SQL>


-- Valid : Extra weight element.
insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":10,"weight":11}'));

1 row created.

SQL>


-- Missing quantity
insert into t1 (id, json_data) values (2, json('{"fruit":"apple"}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>


-- Missing fruit
insert into t1 (id, json_data) values (3, json('{"quantity":10}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>


-- Fruit name too long.
insert into t1 (id, json_data) values (4, json('{"fruit":"abcdefghijk","quantity":10}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>


-- Fruit name too short.
insert into t1 (id, json_data) values (5, json('{"fruit":"","quantity":10}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>


-- Quantity too big.
insert into t1 (id, json_data) values (6, json('{"fruit":"apple","quantity":101}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>


-- Quantity too small.
insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":-1}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>

drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"],
  "additionalProperties"  : false
}',
  constraint t1_pk primary key (id)
);


insert into t1 (id, json_data) values (7, json('{"fruit":"apple","quantity":10,"weight":11}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>
3

VALIDATE Keyword With IS JSON Condition

We can use as part of an IS JSON condition. In the following example we recreate the table, this time using the condition as part of a check contraint. We can also use the keyword with an condition in a query. We recreate the table without using the keyword, and populate it with a variety of JSON documents. We query the table using the condition, so we only return data that matches the JSON schema.

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

create table t1 (
  id         number,
  json_data  json,
  constraint t1_pk primary key (id),
  constraint json_data_chk check (json_data is json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}')
);

drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  json,
  constraint t1_pk primary key (id)
);

insert into t1 (id, json_data) values (1, json('{"fruit":"apple"}'));
insert into t1 (id, json_data) values (2, json('{"quantity":10}'));
insert into t1 (id, json_data) values (3, json('{"fruit":"apple","quantity":10}'));

select *
from   t1
where  json_data is json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}';

        ID JSON_DATA
---------- --------------------------------------------------------------------------------
         3 {"fruit":"apple","quantity":10}

SQL>
4

VALIDATE Keyword With Domains

We can create a domain that uses the keyword to check a JSON schema. This allows us to create a reusable JSON schema. We can use this domain during table creation. There is a simplified form of this type of domain. Notice the check constraint has been removed. We can use this domain during table creation.

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 domain if exists json_schema_domain;

create domain json_schema_domain as json
  constraint json_data_chk check (json_schema_domain is json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}');

drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  domain json_schema_domain
);

drop table if exists t1 purge;
drop domain if exists json_schema_domain;

create domain json_schema_domain as json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}';

create table t1 (
  id         number,
  json_data  domain json_schema_domain
);
5

Extended Data Types

JSON has limited data type support. Native binary JSON data (OSON format) adds support for scalar types not present in JSON. There is a full list of the extended types here . For example, JSON doesn't have datetime data types, so it represents all datetime data as strings. How do we validate that data with a JSON Schema? To demonstrate this we will recreate the test table, altering the JSON Schema to add "expiryDate" as a date extended data type. We try to insert some data. Although this represents a valid date, it is a string, so it violates the JSON schema. We can alter the JSON to explicity identify it as a datatype using "$oracleDate", and add the keyword to the end of the JSON constructor call. We can now insert the original JSON data without the additional annotations, and the schema validation will attempt to cast the extended data types where necessary.

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

create table t1 (
  id         number,
  json_data  json validate '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100},
                  "expiryDate" : {"extendedType": "date"}},
  "required"   : ["fruit", "quantity", "expiryDate"]
}',
  constraint t1_pk primary key (id)
);

insert into t1 (id, json_data) values (1, json('
{
  "fruit" : "apple",
  "quantity" : 10,
  "expiryDate" : "2023-06-30T09:30:26+0000"
}'));
            *
ERROR at line 1:
ORA-40875: JSON schema validation error

SQL>

insert into t1 (id, json_data) values (1, json('
{
  "fruit" : "apple",
  "quantity" : 10,
  "expiryDate" : {"$oracleDate" : "2023-06-30T09:30:26+0000"}
}' extended));

1 row created.

SQL>

drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  json validate cast '{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100},
                  "expiryDate" : {"extendedType": "date"}},
  "required"   : ["fruit", "quantity", "expiryDate"]
}',
  constraint t1_pk primary key (id)
);

insert into t1 (id, json_data) values (1, json('
{
  "fruit" : "apple",
  "quantity" : 10,
  "expiryDate" : "2023-06-30T09:30:26+0000"
}'));
6

DBMS_JSON_SCHEMA.IS_SCHEMA_VALID

The function in the package can check the validity of a JSON schema definition. In the following example we call it with a valid JSON schema, then an invalid one. The function is demonstrated here . 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
select dbms_json_schema.is_schema_valid('{
  "type"       : "object",
  "properties" : {"fruit"    : {"type"      : "string",
                                "minLength" : 1,
                                "maxLength" : 10},
                  "quantity" : {"type"      : "number",
                                "minimum"   : 0,
                                "maximum"   : 100}},
  "required"   : ["fruit", "quantity"]
}') as is_valid;

  IS_VALID
----------
         1

SQL>


select dbms_json_schema.is_schema_valid('banana') as is_valid;
*
ERROR at line 1:
ORA-40441: JSON syntax error


SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!