JSON-Schema - Part III

This will be the continuation of previous blog posts, where we discussed about JSON Schema in two parts (Part I and Part II)
 
Let’s looks again little bit more on this domain, a shorter domain
 
demo@FREEPDB1> create domain if not exists employeeD as json validate '{
  2     "type":"object",
  3     "properties":{
  4             "name":{"type":"string","minLength":2} ,
  5             "salary":{"type":"number","minimum":800} ,
  6             "email":{"type":"string"} ,
  7             "joinDate":{"extendedType":"date"} } ,
  8     "required":["name","salary","joinDate"]
  9  }' ;
 
Operation 281 succeeded.
 
That tell us that the schema used to validate the employee domain is an object, that got three fields “name” that is of string datatype and the minimum length of the string is at least two characters, and the salary filed is numeric datatype and can’t be less than 800 and got an email and the date on which the employee got hired. The field “joinDate” got extendedtype as “date” that maps to the SQL Datatype since “date” datatype is missing in JSON, finally any json documented to be persisted successfully require the keys “name” , “salary” and “joinDate” where as the “email” is the optional
 
Then we associate the above domain to a table.
 
demo@FREEPDB1> create table if not exists
  2  employees( id number, data json domain employeeD );
 
Table created.
 
When we insert the data, it fails
 
demo@FREEPDB1> insert into employees( id,data )
  2  values(1, json('{"name":"john",
  3     "salary":2000,
  4     "joinDate":"2023-05-11T17:04:54+0000"}') );
insert into employees( id,data )
            *
ERROR at line 1:
ORA-40875: JSON schema validation error
 
How do we find out why this data is invalid? this is where the DBMS_JSON_SCHEMA comes in handy, It helps us to analyse the given JSON schema and JSON data and tell us if the document is valid or not and if it is not valid it will list down the errors associated with the documents.
 
demo@FREEPDB1> declare
  2     l_data json ;
  3     l_schema json;
  4     l_result boolean;
  5     l_error json;
  6  begin
  7     l_data := json('{"name":"john",
  8     "salary":2000,
  9     "joinDate":"2023-05-11T17:04:54+0000"}') ;
 10
 11     l_schema := json('{
 12     "type":"object",
 13     "properties":{
 14             "name":{"type":"string","minLength":2} ,
 15             "salary":{"type":"number","minimum":800} ,
 16             "email":{"type":"string"} ,
 17             "joinDate":{"extendedType":"date"} } ,
 18     "required":["name","salary","joinDate"]
 19     }') ;
 20
 21     dbms_json_schema.is_valid( l_data, l_schema, l_result, l_error );
 22     if l_result then
 23             dbms_output.put_line(' Valid data' );
 24     else
 25             dbms_output.put_line( json_serialize( l_error returning clob pretty) );
 26     end if;
 27  end;
 28  /
{
  "valid" : false,
  "errors" :
  [
    {
      "schemaPath" : "$",
      "instancePath" : "$",
      "code" : "JZN-00501",
      "error" : "JSON schema validation failed"
    },
 
    {
      "schemaPath" : "$.properties",
      "instancePath" : "$",
      "code" : "JZN-00514",
      "error" : "invalid properties: 'joinDate'"
    },
    {
      "schemaPath" :
 "$.properties.joinDate.extendedType",
      "instancePath" : "$.joinDate",
      "code" : "JZN-00503",
      "error" : "invalid type found, actual: string, expected: date"
    }
 
]
}
 
PL/SQL procedure successfully completed.
 
To resolve the above highlighed error, how do we express a “date” in JSON document if JSON doesn’t to have a DATE datatype.  One way is to use the JSON constructor in “extended” mode, which means look for the annotations $oracleDate and treat its value (2023-05-11T17:04:54+0000) not as string instead the annotated value (in this case it is “date”)
 
demo@FREEPDB1> insert into employees( id,data )
  2  values(1, json('{"name":"john",
  3     "salary":2000,
  4     "joinDate":{"$oracleDate":"2023-05-11T17:04:54+0000"} }' extended) );
 
1 row created.
 
Now we can validate the datatype from the data using type() methods.
 
demo@FREEPDB1> select e.data.joinDate jd,
  2     e.data.joinDate.date(),
  3     e.data.joinDate.type() ty
  4  from employees e;
 
JD                             E.DATA.JOIN TY
------------------------------ ----------- ----------
2023-05-11T17:04:54            11-MAY-2023 date
 
Another option would be to do in SQL Domain using CAST option
 
demo@FREEPDB1> create domain if not exists employeeD_CAST as json validate CAST '{
  2     "type":"object",
  3     "properties":{
  4             "name":{"type":"string","minLength":2} ,
  5             "salary":{"type":"number","minimum":800} ,
  6             "email":{"type":"string"} ,
  7             "joinDate":{"extendedType":"date"} } ,
  8     "required":["name","salary","joinDate"]
  9  }' ;
 
Operation 281 succeeded.
 
The only difference is the keyword CAST highlighted above. Which means using this JSON Schema not only to validate but also to type CAST data on insert and validate it.
 
Let’s validate the effect of this domain.
 
demo@FREEPDB1> create table if not exists
  2  employees2( id number, data json domain employeeD_CAST );
 
Table created.
 
demo@FREEPDB1>
 
the same statement which used to fail previously was succeed now, because this ISO 8601 data string got casted into a date and got inserted.
 
demo@FREEPDB1> insert into employees2( id,data )
  2  values(1, '{"name":"john",
  3     "salary":2000,
  4     "joinDate":"2023-05-11T17:04:54+0000"}' );
 
1 row created.
 
demo@FREEPDB1> select e.data.joinDate jd,
  2     e.data.joinDate.date(),
  3     e.data.joinDate.type() ty
  4  from employees2 e;
 
JD                             E.DATA.JOIN TY
------------------------------ ----------- ----------
2023-05-11T00:00:00            11-MAY-2023 date
 
But when you put some invalidate string that can’t be casted to date will error like this
 
demo@FREEPDB1> insert into employees2( id,data )
  2  values(2, '{"name":"john",
  3     "salary":2000,
  4     "joinDate":"Hi"}' );
insert into employees2( id,data )
            *
ERROR at line 1:
ORA-40875: JSON schema validation error
JZN-00066: invalid cast operation requested