8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Schema in Oracle 23c
In Oracle 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
- 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"] }
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>
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...