JSON Schema - Part IV

Having discussed enough about the JSON Schema in the previous blog series ( Part I, Part II, Part III ), Another common use of JSON Schema is used to describe an object, lets move on to an example of using it. Here is my EMP table that got very conventional datatypes and no JSON datatypes.
 
demo@FREEPDB1> desc emp
 Name          Null?    Type
 ------------- -------- -------------
 EMPNO         NOT NULL NUMBER(4)
 ENAME                  VARCHAR2(10)
 JOB                    VARCHAR2(9)
 MGR                    NUMBER(4)
 HIREDATE               DATE
 SAL                    NUMBER(7,2)
 COMM                   NUMBER(7,2)
 DEPTNO        NOT NULL NUMBER(2)
 
To get the JSON schema for the above table, all we need to do is make use of “describe” method in DBMS_JSON_SCHEMA package.
 
demo@FREEPDB1> select dbms_json_schema.describe('EMP') x;
 
X
--------------------------------------------------------------------------------
{
  "title" : "EMP",
  "dbObject" : "DEMO.EMP",
  "type" : "object",
  "dbObjectType" : "table",
  "properties" :
  {
    "EMPNO" :
    {
      "extendedType" : "integer",
      "sqlPrecision" : 4
    },
    "ENAME" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 10
    },
    "JOB" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 9
    },
    "MGR" :
    {
      "extendedType" :
      [
        "null",
        "integer"
      ],
      "sqlPrecision" : 4
    },
    "HIREDATE" :
    {
      "extendedType" :
      [
        "null",
        "date"
      ]
    },
    "SAL" :
    {
      "extendedType" :
      [
        "null",
        "number"
      ],
      "sqlPrecision" : 7,
      "sqlScale" : 2
    },
    "COMM" :
    {
      "extendedType" :
      [
        "null",
        "number"
      ],
      "sqlPrecision" : 7,
      "sqlScale" : 2
    },
    "DEPTNO" :
    {
      "extendedType" : "integer",
      "sqlPrecision" : 2
    }
  },
  "required" :
  [
    "EMPNO",
    "DEPTNO"
  ],
  "dbPrimaryKey" :
  [
    "EMPNO"
  ],
  "dbForeignKey" :
  [
    {
      "DEPTNO" :
      {
        "dbObject" : "DEMO.DEPT",
        "dbColumn" : "DEPTNO"
      }
    }
  ]
}
 
 
The above JSON Schema shows the object EMP is of database object type TABLE belonging to the schema DEMO, from a JSON Schema perspective it is an object, but from the relational database object perspective it is table, the dbObjectType can be different based on object type we describe, it can be a type/table/view/dualityview etc.
 
The properties in the JSON Schema maps to the column in the table we described. The EMPNO is actually an integer datatype, where as ENAME is string datatype but it can also be null, similarly all the required columns of the table (column(s) having NOT NULL constraint) are mapped to the required properties of the JSON Schema, similar to that all the key columns in the tables are mapped to dbPrimaryKey and dbForeignKey properties of the JSON Schema
 
Where does this JSON Schema really help us? It is really helpful to validate (both the structure and data typing of JSON documents) the incoming JSON documents for persistence and finally when we generate the JSON for the table EMP and if we need an API description of how the data looks like then this JSON Schema really helps us.
  
Similarly, we can make use of plsql object type for JSON Schema representation.
 
demo@FREEPDB1> create or replace type address_type
  2  as object( street varchar2(30),
  3     city varchar2(20),
  4     state varchar2(2),
  5     postal_code varchar2(6) );
  6  /
 
Type created.
 
The JSON Schema for the plsql object type is possible using.
 
demo@FREEPDB1> select json_serialize( dbms_json_schema.describe('ADDRESS_TYPE') pretty ) ;
 
JSON_SERIALIZE(DBMS_JSON_SCHEMA.DESCRIBE('ADDRESS_TYPE')PRETTY)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
  "title" : "ADDRESS_TYPE",
  "dbObject" : "DEMO.ADDRESS_TYPE",
  "type" : "object",
  "dbObjectType" : "type",
  "properties" :
  {
    "STREET" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 30
    },
    "CITY" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 20
    },
    "STATE" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 2
    },
    "POSTAL_CODE" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 6
    }
  }
}
 
 
The attributes of the object type maps to properties of JSON Schema document. So we can easily describe types/tables/view/dualityviews to get the JSON Schema representation of it.