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:
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}');
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"}');
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}');
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”