In Oracle 23c a JSON Schema can validate the structure and contents of JSON documents in your database.
◉ 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"]
}
◉ 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.
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>
◉ 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.
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 (
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 (
json_data domain json_schema_domain
);
◉ 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>
Source: oracle-base.com
Posts
0 comments:
Post a Comment