<< 返回文章列表

Oracle 19c 新特性详解:JSON_OBJECT 的查询简化和增强

2019年2月20日
盖国强
2134

自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 的简化,让代码可以更加优雅简洁。