Last active
July 27, 2020 23:03
-
-
Save marantz/df4a8474cffad142cd2cbfd388ce709b to your computer and use it in GitHub Desktop.
Oracle SQL to json
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 1) json_object | |
| 2) json_objectagg | |
| 3) json_array | |
| 4) json_arrayagg | |
| SELECT JSON_OBJECT ( | |
| KEY 'deptno' IS d.department_id FORMAT JSON, | |
| KEY 'deptname' IS d.department_name FORMAT JSON | |
| ) "Department Objects" | |
| FROM departments d | |
| ORDER BY d.department_id; | |
| Department Objects | |
| ---------------------------------------- | |
| {"deptno":10,"deptname":Administration} | |
| {"deptno":20,"deptname":Marketing} | |
| {"deptno":30,"deptname":Purchasing} | |
| {"deptno":40,"deptname":Human Resources} | |
| {"deptno":50,"deptname":Shipping} | |
| . . . | |
| SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) "Department Numbers" | |
| FROM departments | |
| WHERE department_id <= 30; | |
| Department Numbers | |
| ---------------------------------------------------- | |
| {"Administration":10,"Marketing":20,"Purchasing":30} | |
| SELECT JSON_ARRAY ( | |
| JSON_OBJECT('percentage' VALUE .50), | |
| JSON_ARRAY(1,2,3), | |
| 100, | |
| 'California', | |
| null | |
| NULL ON NULL | |
| ) "JSON Array Example" | |
| FROM DUAL; | |
| JSON Array Example | |
| -------------------------------------------------------------------------------- | |
| [{"percentage":0.5},[1,2,3],100,"California",null] | |
| CREATE TABLE id_table (id NUMBER); | |
| INSERT INTO id_table VALUES(624); | |
| INSERT INTO id_table VALUES(null); | |
| INSERT INTO id_table VALUES(925); | |
| INSERT INTO id_table VALUES(585); | |
| SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS | |
| FROM id_table; | |
| ID_NUMBERS | |
| ------------- | |
| [585,624,925] | |
| SELECT JSON_OBJECT( | |
| ‘cust_no' VALUE CUST_NO | |
| ,'fee_levy_typ_cd' VALUE FEE_LEVY_TYP_CD | |
| ,'limit_ym' VALUE LIMIT_YM | |
| ,'step' VALUE STEP | |
| ,'busi_knd_cd' VALUE BUSI_KND_CD | |
| ,'w_d_typ_cd' VALUE W_D_TYP_CD | |
| ,'uwater_yn' VALUE UWATER_YN | |
| ,'limit_day' VALUE LIMIT_DAY | |
| ,'ctl_qty' VALUE CTL_QTY | |
| ,'ctl_amt' VALUE CTL_AMT | |
| ,'reger_sn' VALUE REGER_SN | |
| ,'reg_date' VALUE REG_DATE | |
| ,'upter_sn' VALUE UPTER_SN | |
| ,'upt_date' VALUE UPT_DATE | |
| ,'caliber_cd' VALUE CALIBER_CD ABSENT ON NULL) AS JSON | |
| FROM WC405 | |
| ORDER BY CUST_NO, LIMIT_YM; | |
| select json_object ( | |
| 'department' value d.department_name, | |
| 'employees' value json_arrayagg ( | |
| json_object ( | |
| 'name' value first_name || ',' || last_name, | |
| 'job' value job_title ))) | |
| from (select * from hr.departments fetch first 10 rows only) d, hr.employees e, hr.jobs j | |
| where d.department_id = e.department_id | |
| and e.job_id = j.job_id | |
| group by d.department_name; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment