- Overview:
- Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data in Oracle 12.2, but without the option to validate the structure of JSON document.
- Oracle Database 23ai introduces a new feature called JSON Schema.
- A JSON Schema can validate the structure and contents of JSON documents in your database when defining a JSON column in our table.
- A JSON Schema is a declarative language that allows us to annotate and validate JSON documents, which helps avoid errors in production that were missed in development.
- A JSON schema specifies the allowed properties for JSON documents.
- A JSON Schema validation is available also as a PL/SQL utility function.
In this blog, I'll demonstrate the use of the VALIDATE clause along with the JSON schema when defining a JSON column in our table.
- Prerequisites:
Demo #1: A simple validation using the IS JSON keywords
1. Create a table with a column, which has a JSON datatype with a check constraint.
A "IS JSON VALIDATE USING" clause will be used for check constraint validation.
create table json_tab (
col1 json constraint json_tab_col1_isjson check (col1 is json validate using
'{
"type":"object",
"minProperties":2
}')
);
2. Insert JSON data into the table.
- Insert invalid JSON data by inserting an array. This will raise ORA-40875 error.
insert into json_tab values ('["a","b"]');
- Insert valid JSON data.
insert into json_tab values ('{"a":1,"b":2}');
Demo #2: A simple validation using the shorthand syntax without the constraint keyword
1. Create a table with a column, which has a JSON datatype without a check constraint.
A "VALIDATE USING" clause will be used for JSON document validation.
The JSON document has two properties price (number) and name (string).
create table json_tab2 (
col1 json validate using
'{
"type":"object",
"properties": {"price":{"type":"number"},
"name": {"type":"string"}
}
}'
);
2. Insert JSON data into the table.
- Insert invalid JSON data by inserting a JSON document with invalid price property value datatype. This will raise ORA-40875 error.
insert into json_tab2 values ('{"price":"ten", "name":"widget"}'); --- price property value is string
- Insert valid JSON data.
insert into json_tab2 values ('{"price":10, "name":"widget"}');
Demo #3: A simple validation using SQL-Domain based JSON Validation Rules
1. Create SQL-Domain with a column, which has a JSON datatype without a check constraint.
A "VALIDATE USING" clause will be used for JSON document validation.
The JSON document has two required properties width and height where both properties have a number datatype and minimum and maximum values.
create domain json_size_domain as json validate using
' { "type": "object",
"required": [ "width", "height" ],
"properties": {
"width": { "type": "number", "minimum":20, "maximum": 62 },
"height": { "type": "number", "minimum":25, "maximum": 50 }
}
} ';
2. Create a table with a column, which has a JSON datatype and uses the created domain.
create table json_tab3 (col1 json domain json_size_domain);
3. Insert JSON data into the table.
- Insert invalid JSON data by inserting a JSON document where width property has invalid value (< minimum value). This will raise ORA-40875 error.
insert into json_tab3 values ('{"width":0, "height":49}'); --- width value < 20 (minimum value)
- Insert valid JSON data.
insert into json_tab3 values ('{"width":30, "height":49}');
Demo #4: Validate using the VALIDATE_REPORT utility Function
1. Call VALIDATE_REPORT utility function on an invalid JSON document. The function will report back "valid":"false" with the error message.
2. Call VALIDATE_REPORT utility function on a valid JSON document. The function will report back "valid":"true"
