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:
- Oracle Database 23ai
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 the 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 an invalid price property value datatype. This will raise the 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 the width property has an invalid value (< minimum value). This will raise the 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 the VALIDATE_REPORT utility function on an invalid JSON document. The function will report back “valid”: “false” with the error message.
2. Call the VALIDATE_REPORT utility function on a valid JSON document. The function will report back “valid”: “true”