Oracle #JSON check constraint #quicktip

I came across an interesting problem, we are using JSON in one of our applications and have no control over the people sending us the JSON data. This has caused some issues with data quality. One thing we needed to do is to check that all the required fields have a value when the data is loaded. I found solving the problem to be quite simple by adding check constraints on the JSON column.

alter table payload add constraint 
payload_first_name_check check
(json_exists(json_document, ‘$.first_name));

alter table payload add constraint
payload_last_name_check check
(json_exists(json_document, ‘$.last_name’));

Now when the documents load, if the first or last name are null, the check constraint is violated and ORA-02290  will be raised.

 Failed to execute: soda insert payload /home/oracle/data/04.json

 java.sql.SQLIntegrityConstraintViolationException: ORA-02290: check constraint (RLOCKARD.SYS_C0012523) violated

#Oracle #SQLcl #quicktip

If you’re not following Jeff Smith and Kris Rice, you should be. These are two really smart guys, who are designing the tools we use everyday to do our jobs.

If you’re like me, 99% of your data is in relational tables. So how do you get the data out of your relational tables into a JSON format. SQLcl has a command for that.

Getting data out of your relational tables and into JSON format is pretty easy; only, up until now, it was not easy to read. 

set sqlformat json-formattedSQL> select * from hr.employees  2* where rownum = 1;{“results”:[{“columns”:[{“name”:”EMPLOYEE_ID”,”type”:”NUMBER”},{“name”:”FIRST_NAME”,”type”:”VARCHAR2″},{“name”:”LAST_NAME”,”type”:”VARCHAR2″},{“name”:”EMAIL”,”type”:”VARCHAR2″},{“name”:”PHONE_NUMBER”,”type”:”VARCHAR2″},{“name”:”HIRE_DATE”,”type”:”DATE”},{“name”:”JOB_ID”,”type”:”VARCHAR2″},{“name”:”SALARY”,”type”:”NUMBER”},{“name”:”COMMISSION_PCT”,”type”:”NUMBER”},{“name”:”MANAGER_ID”,”type”:”NUMBER”},{“name”:”DEPARTMENT_ID”,”type”:”NUMBER”}],”items”:[{“employee_id”:100,”first_name”:”Steven”,”last_name”:”King”,”email”:”SKING”,”phone_number”:”515.123.4567″,”hire_date”:”17-JUN-87″,”job_id”:”AD_PRES”,”salary”:24000,”department_id”:90}]}]}

json-formatted is new in SQLcl 19.2 this gives a pretty print of the JSON data.

<code>
SQL> set sqlformat json-formatted
SQL> select * from hr.employees where rownum = 1;
{
  “results” : [
    {
      “columns” : [
        {
          “name” : “EMPLOYEE_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “FIRST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “LAST_NAME”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “EMAIL”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “PHONE_NUMBER”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “HIRE_DATE”,
          “type” : “DATE”
        },
        {
          “name” : “JOB_ID”,
          “type” : “VARCHAR2”
        },
        {
          “name” : “SALARY”,
          “type” : “NUMBER”
        },
        {
          “name” : “COMMISSION_PCT”,
          “type” : “NUMBER”
        },
        {
          “name” : “MANAGER_ID”,
          “type” : “NUMBER”
        },
        {
          “name” : “DEPARTMENT_ID”,
          “type” : “NUMBER”
        }
      ],
      “items” : [
        {
          “employee_id” : 100,
          “first_name” : “Steven”,
          “last_name” : “King”,
          “email” : “SKING”,
          “phone_number” : “515.123.4567”,
          “hire_date” : “17-JUN-87”,
          “job_id” : “AD_PRES”,
          “salary” : 24000,
          “commission_pct” : “”,
          “manager_id” : “”,
          “department_id” : 90
        }
      ]
    }
  ]
}
</code>