8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Schema in Oracle Database 23c
In Oracle database 23c a JSON Schema can validate the structure and contents of JSON documents in your database.
- What is a JSON Schema?
- VALIDATE Keyword During Table Creation
- VALIDATE Keyword With IS JSON Condition
- VALIDATE Keyword With Domains
- Extended Data Types
- DBMS_JSON_SCHEMA.IS_SCHEMA_VALID
Related articles.
- Precheck JSON Constraints in Oracle Database 23c
- JSON Data Type in Oracle Database 21c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
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.
{
"type" : "object",
"properties" : {"fruit" : {"type" : "string",
"minLength" : 1,
"maxLength" : 10},
"quantity" : {"type" : "number",
"minimum" : 0,
"maximum" : 100}},
"required" : ["fruit", "quantity"]
}
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 JSON_DATAGUIDE function in Oracle 18c (here). In both cases this the ability to display a JSON Schema, not enforce one.
VALIDATE Keyword During Table Creation
We use the VALIDATE clause along with the JSON schema when defining a JSON column in our table.
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)
);
We can see the JSON schema is associated with the column by using the USER_JSON_SCHEMA_COLUMNS view.
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>
We create some data to test the JSON schema.
-- 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>
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.
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>
VALIDATE Keyword With IS JSON Condition
We can use VALIDATE as part of an IS JSON condition. In the following example we recreate the table, this time using the IS JSON condition as part of a check contraint.
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"]
}')
);
We can also use the VALIDATE keyword with an IS JSON condition in a query. We recreate the table without using the VALIDATE keyword, and populate it with a variety of JSON documents.
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}'));
We query the table using the IS JSON VALIDATE condition, so we only return data that matches the JSON schema.
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>
VALIDATE Keyword With Domains
We can create a domain that uses the VALIDATE keyword to check a JSON schema. This allows us to create a reusable JSON schema.
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"]
}');
We can use this domain during table creation.
drop table if exists t1 purge; create table t1 ( id number, json_data domain json_schema_domain );
There is a simplified form of this type of domain. Notice the check constraint has been removed.
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"]
}';
We can use this domain during table creation.
create table t1 ( id number, json_data domain json_schema_domain );
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.
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)
);
We try to insert some data.
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>
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 DATE datatype using "$oracleDate", and add the EXTENDED keyword to the end of the JSON constructor call.
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>
DBMS_JSON_SCHEMA.IS_SCHEMA_VALID
The IS_SCHEMA_VALID function in the DBMS_JSON_SCHEMA 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.
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>
The DESCRIBE function is demonstrated here.
For more information see:
- JSON Schema
- Precheck JSON Constraints in Oracle Database 23c
- JSON Data Type in Oracle Database 21c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...