<< 返回文章列表
Oracle 19c 新特性详解:JSON_OBJECT 的查询简化和增强
2019年2月20日
盖国强
2171
自Oracle Database 12.2以来,我们能够使用以下四个SQL运算符JSON_OBJECT,JSON_OBJECTAGG,JSON_ARRAY和JSON_ARRAYAGG之一生成JSON数据。
在下面的示例中,如果期望为scott.emp表中的每一行创建一个单独的JSON文档,查询类似如下:
SELECT JSON_OBJECT('EMPNO' VALUE empno, 'ENAME' VALUE ename, 'JOB' VALUE job, 'MGR' VAULE mgr,'HIREDATE' VALUE hiredate, 'SAL' VALUE sal, 'COMM' VALUE com, 'DEPTNO' VALUE deptno ) FROM scott.emp; {"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":NULL, "HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM":NULL, "DEPTNO":10}{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-05-01T00:00:00","SAL":2850,"COMM":NULL, "DEPTNO":30}{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839, "HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM":NULL, "DEPTNO":10}
在Oracle 19c 中做出了简化,如果要查询一个表中所有列,则不再需要制定列名,请看如下测试:
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> create table enmotech as select username,created from dba_users; Table created. SQL> select json_object(*) from enmotech; JSON_OBJECT(*) -------------------------------------------------------------------------------- {"USERNAME":"SYS","CREATED":"2018-11-16T10:58:42"} {"USERNAME":"SYSTEM","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"XS$NULL","CREATED":"2018-11-16T11:04:40"} {"USERNAME":"OJVMSYS","CREATED":"2018-11-16T11:42:51"} {"USERNAME":"LBACSYS","CREATED":"2018-11-16T12:00:18"} {"USERNAME":"OUTLN","CREATED":"2018-11-16T10:58:51"} {"USERNAME":"SYS$UMF","CREATED":"2018-11-16T11:12:06"} {"USERNAME":"DBSNMP","CREATED":"2018-11-16T11:16:43"} {"USERNAME":"APPQOSSYS","CREATED":"2018-11-16T11:16:45"} {"USERNAME":"DBSFWUSER","CREATED":"2018-11-16T11:04:50"} {"USERNAME":"GGSYS","CREATED":"2018-11-16T11:17:09"} JSON_OBJECT(*) -------------------------------------------------------------------------------- {"USERNAME":"ANONYMOUS","CREATED":"2018-11-16T11:19:35"} {"USERNAME":"CTXSYS","CREATED":"2018-11-16T11:45:53"} {"USERNAME":"DVSYS","CREATED":"2018-11-16T12:00:37"} {"USERNAME":"DVF","CREATED":"2018-11-16T12:00:37"} {"USERNAME":"GSMADMIN_INTERNAL","CREATED":"2018-11-16T11:04:01"} {"USERNAME":"MDSYS","CREATED":"2018-11-16T11:46:53"} {"USERNAME":"OLAPSYS","CREATED":"2018-11-16T11:50:49"} {"USERNAME":"XDB","CREATED":"2018-11-16T11:19:35"} {"USERNAME":"WMSYS","CREATED":"2018-11-16T11:40:52"} {"USERNAME":"GSMCATUSER","CREATED":"2018-11-16T11:17:05"} {"USERNAME":"MDDATA","CREATED":"2018-11-16T11:51:16"} JSON_OBJECT(*) -------------------------------------------------------------------------------- {"USERNAME":"SYSBACKUP","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"REMOTE_SCHEDULER_AGENT","CREATED":"2018-11-16T11:04:49"} {"USERNAME":"GSMUSER","CREATED":"2018-11-16T11:04:01"} {"USERNAME":"SYSRAC","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"GSMROOTUSER","CREATED":"2018-11-16T11:04:01"} {"USERNAME":"SI_INFORMTN_SCHEMA","CREATED":"2018-11-16T11:46:53"} {"USERNAME":"AUDSYS","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"DIP","CREATED":"2018-11-16T11:04:10"} {"USERNAME":"ORDPLUGINS","CREATED":"2018-11-16T11:46:53"} {"USERNAME":"SYSKM","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"ORDDATA","CREATED":"2018-11-16T11:46:53"} JSON_OBJECT(*) -------------------------------------------------------------------------------- {"USERNAME":"ORACLE_OCM","CREATED":"2018-11-16T11:05:58"} {"USERNAME":"SYSDG","CREATED":"2018-11-16T10:58:43"} {"USERNAME":"ORDSYS","CREATED":"2018-11-16T11:46:52"} 36 rows selected.
SQL 的简化,让代码可以更加优雅简洁。