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.