JSON-Schema - Part II

JSON Schema is a powerful tool for validating the structure of JSON data, the JSON Schema discussed in the pervious post is quite long (long check constraint), however there is another way to make it quite shorter using a reusable component called SQL Domains, obviously we need to associate the  JSON Schema with the SQL Domains.
 
demo@FREEPDB1> create domain orders_js_domain as JSON validate ' {
  2    "type" : "object",
  3    "o:length" : 1,
  4    "properties" :
  5    {
  6      "User" :
  7      {
  8        "type" : "string",
  9        "o:length" : 8,
 10        "o:preferred_column_name" : "User"
 11      },
 12      "PONumber" :
 13      {
 14        "type" : "number",
 15        "o:length" : 4,
 16        "o:preferred_column_name" : "PONumber"
 17      },
 18      "LineItems" :
 19      {
 20        "type" : "array",
 21        "o:length" : 1,
 22        "o:preferred_column_name" : "LineItems",
 23        "items" :
 24        {
 25          "properties" :
 26          {
 27            "Part" :
 28            {
 29              "type" : "object",
 30              "o:length" : 1,
 31              "o:preferred_column_name" : "Part",
 32              "properties" :
 33              {
 34                "UPCCode" :
 35                {
 36                  "type" : "number",
 37                  "o:length" : 8,
 38                  "o:preferred_column_name" : "UPCCode"
 39                },
 40                "UnitPrice" :
 41                {
 42                  "type" : "number",
 43                  "o:length" : 4,
 44                  "o:preferred_column_name" : "UnitPrice"
 45                },
 46                "Description" :
 47                {
 48                  "type" : "string",
 49                  "o:length" : 32,
 50                  "o:preferred_column_name" : "Description"
 51                }
 52              }
 53            },
 54            "Quantity" :
 55            {
 56              "type" : "number",
 57              "o:length" : 2,
 58              "o:preferred_column_name" : "Quantity"
 59            },
 60            "ItemNumber" :
 61            {
 62              "type" : "number",
 63              "o:length" : 2,
 64              "o:preferred_column_name" : "ItemNumber"
 65            }
 66          }
 67        }
 68      },
 69      "Reference" :
 70      {
 71        "type" : "string",
 72        "o:length" : 16,
 73        "o:preferred_column_name" : "Reference"
 74      },
 75      "Requestor" :
 76      {
 77        "type" : "string",
 78        "o:length" : 16,
 79        "o:preferred_column_name" : "Requestor"
 80      },
 81      "CostCenter" :
 82      {
 83        "type" : "string",
 84        "o:length" : 4,
 85        "o:preferred_column_name" : "CostCenter"
 86      },
 87      "AllowPartialShipment" :
 88      {
 89        "type" : "boolean",
 90        "o:length" : 8,
 91        "o:preferred_column_name" : "AllowPartialShipment"
 92      },
 93      "ShippingInstructions" :
 94      {
 95        "type" : "object",
 96        "o:length" : 1,
 97        "o:preferred_column_name" : "ShippingInstructions",
 98        "properties" :
 99        {
100          "name" :
101          {
102            "type" : "string",
103            "o:length" : 16,
104            "o:preferred_column_name" : "name"
105          },
106          "Phone" :
107          {
108            "type" : "array",
109            "o:length" : 1,
110            "o:preferred_column_name" : "Phone",
111            "items" :
112            {
113              "properties" :
114              {
115                "type" :
116                {
117                  "type" : "string",
118                  "o:length" : 8,
119                  "o:preferred_column_name" : "type"
120                },
121                "number" :
122                {
123                  "type" : "string",
124                  "o:length" : 16,
125                  "o:preferred_column_name" : "number"
126                }
127              }
128            }
129          },
130          "Address" :
131          {
132            "type" : "object",
133            "o:length" : 1,
134            "o:preferred_column_name" : "Address",
135            "properties" :
136            {
137              "city" :
138              {
139                "type" : "string",
140                "o:length" : 32,
141                "o:preferred_column_name" : "city"
142              },
143              "state" :
144              {
145                "type" : "string",
146                "o:length" : 2,
147                "o:preferred_column_name" : "state"
148              },
149              "street" :
150              {
151                "type" : "string",
152                "o:length" : 32,
153                "o:preferred_column_name" : "street"
154              },
155              "country" :
156              {
157                "type" : "string",
158                "o:length" : 32,
159                "o:preferred_column_name" : "country"
160              },
161              "zipCode" :
162              {
163                "type" : "number",
164                "o:length" : 4,
165                "o:preferred_column_name" : "zipCode"
166              }
167            }
168          }
169        }
170      },
171      "Special Instructions" :
172      {
173        "type" : ["null","string"],
174        "o:length" : 1,
175        "o:preferred_column_name" : "Special Instructions"
176      }
177    } , "additionalProperties":false
178  } ' ;
 
Operation 281 succeeded.
 
 
Then we can associate this domain to the datatype of the column in a table
 
demo@FREEPDB1> create table if not exists orders2(  order_id number,
  2     data json domain orders_js_domain );
 
Table created.
 
Then inserting JSON document violating the associate JSON schema will fail like this.
 
demo@FREEPDB1> insert into orders2(order_id,data) values( 1,json('{"hello":"world"}') );
insert into orders2( order_id,data) values( 1,json('{"hello":"world"}') )
            *
ERROR at line 1:
ORA-40875: JSON schema validation error
 
Inserting a JSON document matching the associate JSON schema will get succeed like this
 
demo@FREEPDB1> insert into orders2( order_id,data) values( 7657, json('{ "PONumber" : 7657,
  2    "Reference"            : "KPARTNER-20140326",
  3    "Requestor"            : "Karen Partners",
  4    "User"                 : "KPARTNER",
  5    "CostCenter"           : "A80",
  6    "ShippingInstructions" : { "name"   : "Karen Partners",
  7                               "Address": { "street"  : "Magdalen Centre, this Isis Science Park",
  8                                            "city"    : "Oxford",
  9                                            "state"   : "OX",
 10                                            "zipCode" : 99236,
 11                                            "country" : "United Kingdom" },
 12                               "Phone" : [ { "type"   : "Office",
 13                                             "number" : "824-555-4176" },
 14                                           { "type"   : "Mobile",
 15                                             "number" : "415-555-1234" } ] },
 16    "Special Instructions" : "Courier",
 17    "AllowPartialShipment" : false,
 18    "LineItems"            : [ { "ItemNumber" : 1,
 19                                 "Part"       : { "Description" : "Black Dog",
 20                                                  "UnitPrice"   : 19.95,
 21                                                  "UPCCode"     : 25192039126 },
 22                                 "Quantity"   : 5 },
 23                               { "ItemNumber" : 2,
 24                                 "Part"       : { "Description" : "Princess Mononoke",
 25                                                  "UnitPrice"   : 19.95,
 26                                                  "UPCCode"     : 717951007414 },
 27                                 "Quantity"   : 2 },
 28                                                     { "ItemNumber" : 3,
 29                                 "Part"       : { "Description" : "Shanghai Triad",
 30                                                  "UnitPrice"   : 19.95,
 31                                                  "UPCCode"     : 43396118577 },
 32                                 "Quantity"   : 2 } ] } ') );
 
1 row created.
 
demo@FREEPDB1>
 
by this way, the JSON Schema can be associated with a domain and can be shared across all the application entities. 

JSON-Schema - Part I

JSON Schema is a declarative language that allows to annotate and validate JSON documents, it enables a confident and reliable use of the JSON data format. Benefits of JSON schema includes describes your existing data format, provides clear human and machine readable documentation & validates the data which is useful for automated testing & ensuring the quality of client submitted data. It is similar to XML schema (aka DTD)
 
JSON Schema support in Oracle database is not something new in Oracle 23c instead is it available in Oracle 19c as JSON Dataguide (introduced in 12.2.0.1 and got enhanced greatly in subsequent release) to support automatic creation of relational views. In Oracle 23c this JSON Schema is enhanced further to
 

• validation and type casting
• describe database object using JSON schema
• It became base for PRECHECK constraints.

 
Let’s get started with a demo of using JSON schema into action.
 
Got a table “orders” loaded with 1000 JSON documents in it
 
demo@FREEPDB1> select count(*) from orders ;
 
  COUNT(*)
----------
     10000
 
demo@FREEPDB1> desc orders
 Name               Null?    Type
 ------------------ -------- ----------
 ORDER_ID                    NUMBER
 DATA                        JSON
 
Now we generate JSON Schema for all these rows, using json_dataguide function , that scan all these documents and provide a JSON Schema for the instance data we see.
 
demo@FREEPDB1> select json_dataguide( data, dbms_json.FORMAT_HIERARCHICAL,dbms_json.pretty ) from orders;
 
JSON_DATAGUIDE(DATA,DBMS_JSON.FORMAT_HIERARCHICAL,DBMS_JSON.PRETTY)
--------------------------------------------------------------------------------
{
  "type" : "object",
  "o:length" : 1,
  "properties" :
  {
    "User" :
    {
      "type" : "string",
      "o:length" : 8,
      "o:preferred_column_name" : "User"
    },
    "PONumber" :
    {
      "type" : "number",
      "o:length" : 4,
      "o:preferred_column_name" : "PONumber"
    },
    "LineItems" :
    {
      "type" : "array",
      "o:length" : 1,
      "o:preferred_column_name" : "LineItems",
      "items" :
      {
        "properties" :
        {
          "Part" :
          {
            "type" : "object",
            "o:length" : 1,
            "o:preferred_column_name" : "Part",
            "properties" :
            {
              "UPCCode" :
              {
                "type" : "number",
                "o:length" : 8,
                "o:preferred_column_name" : "UPCCode"
              },
              "UnitPrice" :
              {
                "type" : "number",
                "o:length" : 4,
                "o:preferred_column_name" : "UnitPrice"
              },
              "Description" :
              {
                "type" : "string",
                "o:length" : 32,
                "o:preferred_column_name" : "Description"
              }
            }
          },
          "Quantity" :
          {
            "type" : "number",
            "o:length" : 2,
            "o:preferred_column_name" : "Quantity"
          },
          "ItemNumber" :
          {
            "type" : "number",
            "o:length" : 2,
            "o:preferred_column_name" : "ItemNumber"
          }
        }
      }
    },
    "Reference" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "Reference"
    },
    "Requestor" :
    {
      "type" : "string",
      "o:length" : 16,
      "o:preferred_column_name" : "Requestor"
    },
    "CostCenter" :
    {
      "type" : "string",
      "o:length" : 4,
      "o:preferred_column_name" : "CostCenter"
    },
    "AllowPartialShipment" :
    {
      "type" : "boolean",
      "o:length" : 8,
      "o:preferred_column_name" : "AllowPartialShipment"
    },
    "ShippingInstructions" :
    {
      "type" : "object",
      "o:length" : 1,
      "o:preferred_column_name" : "ShippingInstructions",
      "properties" :
      {
        "name" :
        {
          "type" : "string",
          "o:length" : 16,
          "o:preferred_column_name" : "name"
        },
        "Phone" :
        {
          "type" : "array",
          "o:length" : 1,
          "o:preferred_column_name" : "Phone",
          "items" :
          {
            "properties" :
            {
              "type" :
              {
                "type" : "string",
                "o:length" : 8,
                "o:preferred_column_name" : "type"
              },
              "number" :
              {
                "type" : "string",
                "o:length" : 16,
                "o:preferred_column_name" : "number"
              }
            }
          }
        },
        "Address" :
        {
          "type" : "object",
          "o:length" : 1,
          "o:preferred_column_name" : "Address",
          "properties" :
          {
            "city" :
            {
              "type" : "string",
              "o:length" : 32,
              "o:preferred_column_name" : "city"
            },
            "state" :
            {
              "type" : "string",
              "o:length" : 2,
              "o:preferred_column_name" : "state"
            },
            "street" :
            {
              "type" : "string",
              "o:length" : 32,
              "o:preferred_column_name" : "street"
            },
            "country" :
            {
              "type" : "string",
              "o:length" : 32,
              "o:preferred_column_name" : "country"
            },
            "zipCode" :
            {
              "type" : "number",
              "o:length" : 4,
              "o:preferred_column_name" : "zipCode"
            }
          }
        }
      }
    },
    "Special Instructions" :
    {
      "type" : "null",
      "o:length" : 1,
      "o:preferred_column_name" : "Special Instructions"
    }
  }
}
 
 
Lets look at the JSON schema in high level
 
{
  "type" : "object",
  "o:length" : 1,
  "properties" :
  {
    "User" :
    {
      "type" : "string",
      "o:length" : 8,
      "o:preferred_column_name" : "User"
    },
    "PONumber" :
    {
      "type" : "number",
      "o:length" : 4,
      "o:preferred_column_name" : "PONumber"
    },
 
The "type" : "object", tell us that the data we look here is an JSON object, which has the attributes like "User" and "PONumber" that are of the datatype varchar2 and number with “Preferred column name” as “User” and “PONumber” and these “Preferred column name” will be used as column name when these JSON data is exposed as relational views.
 
In short  the JSON schema produced is all about listing the fields in the documents along with its datatype.
 
In Oracle 23c we can add this JSON schema as a declarative constraint to the data model.
 
demo@FREEPDB1> alter table orders
  2  add constraint orders_json_chk
  3  check( data is json validate using q'# {
  4    "type" : "object",
  5    "o:length" : 1,
  6    "properties" :
  7    {
  8      "User" :
  9      {
 10        "type" : "string",
 11        "o:length" : 8,
 12        "o:preferred_column_name" : "User"
 13      }
……………
……………
……………
173      "Special Instructions" :
174      {
175        "type" : "null",
176        "o:length" : 1,
177        "o:preferred_column_name" : "Special Instructions"
178      }
179    }
180  } #' ) ;
 
Table altered.
 
 
Now, lets see how this affect the validation during data insertion.
 
demo@FREEPDB1> insert into orders( order_id,data) values( 10123,json('{"hello":"world"}') );
 
1 row created.
 
we expect this insert statement to fail post the JSON schema added to the table because the JSON document persisted does not map to the Keys in the JSON schema. but that was not the case here, it will succeed. the reason is JSON is going to be schema flexible, and got no constraint about the "Keys" it has. and since JSON Schema says the top-level of document to persist is "object" and that matches to this input, hence it allows this document to persist.
 
demo@FREEPDB1> insert into orders( order_id,data) values( 10123,json[1,2,3] );
insert into orders( order_id,data) values( 10123,json[1,2,3] )
*
ERROR at line 1:
ORA-40875: JSON schema validation error
 
this insert got failed, since JSON Schema says the top-level of document to persist is "object" not array, so that confirms that some validation is happening upon json document using the constrained JSON schema.
 
demo@FREEPDB1> insert into orders( order_id,data) values( 10123, json{'Special Instructions':true} );
insert into orders( order_id,data) values( 10123, json{'Special Instructions':true} )
            *
ERROR at line 1:
ORA-40875: JSON schema validation error
 
this got failed, since "Special Instructions" can have either string or null (as per the constrained JSON schema) but not a Boolean value
 
To constraint the “KEYS” in JSON document, we need to redefine the Schema as
 
demo@FREEPDB1> alter table orders
  2  drop constraint orders_json_chk;
 
Table altered.
 
demo@FREEPDB1> alter table orders
  2  add constraint orders_json_chk
  3  check( data is json validate using q'# {
  4    "type" : "object",
  5    "o:length" : 1,
  6    "properties" :
  7    {
  8      "User" :
  9      {
 10        "type" : "string",
 11        "o:length" : 8,
 12        "o:preferred_column_name" : "User"
 13      },
 14      "PONumber" :
 15      {
 16        "type" : "number",
 17        "o:length" : 4,
 18        "o:preferred_column_name" : "PONumber"
 19      },
………………………………………..
………………………………………..
………………………………………..
173      "Special Instructions" :
174      {
175        "type" : "null",
176        "o:length" : 1,
177        "o:preferred_column_name" : "Special Instructions"
178      }
179    } , "additionalProperties":false
180  } #' ) ;
 
Table altered.
 
The additionalProperties keyword is used to control the handling of extra stuff, that is, properties whose names are not listed in the “properties” keyword, The value of the additionalProperties keyword is a schema that will be used to validate any “properties” in the instance that are not matched by properties. Setting the additionalProperties schema to false means no additional properties will be allowed
 
Once the constraint got recreated with “additionalProperties” of JSON schema set to false, and now repeating the first Insert statement fail like this
 
demo@FREEPDB1> insert into orders( order_id,data) values( 10123,json('{"hello":"world"}') );
insert into orders( order_id,data) values( 10123,json('{"hello":"world"}') )
            *
ERROR at line 1:
ORA-40875: JSON schema validation error
 
 
The reason behind this constraint violation error is, the JSON document got the key “hello” but that is not present in the JSON schema document mapped to the constraint,