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

json

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"]');

json2

    – Insert valid JSON data

       insert into json_tab values ('{"a":1,"b":2}');

json3

 

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

json4

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

json5

– Insert valid JSON data

   insert into json_tab2 values ('{"price":10, "name":"widget"}');

json6

json7

 

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);

json8

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) 

json9

– Insert valid JSON data.

insert into json_tab3 values ('{"width":30, "height":49}');

json10

 

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.

json11

2. Call the VALIDATE_REPORT utility function on a valid JSON document. The function will report back “valid”: “true” 

json12