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> |