Oracle 23ai: JSON Schema Support

 

- 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 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"