Oracle 12c new Feature: JSON Support in the Oracle 12c Database RDBMS(从此oracle rdbms支持JSON)

很久前在国外的一个blog就看到说是oracle RDBMS(not NOSQL)要支持JSON, 当时说是XMLDB 开发组在做这个事情，随着oracle 12.1.0.2 release 得到确认，的确引入这一特性，JSON, JavaScript Ojbect Notation 在结构上和XML有很多相似之处, 而且这种Schema less data management的数据模型在NOSQL 中广泛应用，数据的存储相对更加紧凑，避免有时空列致ORACLE RDBMS中表一些数据"洞"， 目前也有用MongoDB,开发的角度说是json 对于数据展现上更加方便。
看OOW上XMLDB development team提到目标是创造一种关系型数据库的JSON,包留关系数据库的特点比如sql,join,index,启用一种数据优先schema的schema-flexible 灵活开发。但是JSON 不会像varchar2一样做为一种新的datatype引入,而是用现用的数据类型来变相支持JSON. 创建一种名为SQL/JSON 查询标准。
使用已有的数据类型支持JSON,包括(N)varchar2,(N)clob,raw,blob,bfile；同时创建一种CHECK 约束来保证只有JSON格式存储，支持OCI,JDBC, .NET 流行的API操作JSON. 同时提供了一些方法如JSON_VALUE,JSON_QUERY, JSON_EXISTS, JSON_TABLE...
下面创建个简单的例子

sys@ORA12102>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
-------------------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12102 MOUNTED
sys@ORA12102>alter pluggable database pdb12102 open;
Pluggable database altered.
sys@ORA12102>alter session set container=pdb12102;
Session altered.
sys@ORA12102>create user anbob identified by anbob;
User created.
sys@ORA12102>grant create session,create table to anbob;
Grant succeeded.
sys@ORA12102>alter user anbob quota unlimited on users;
User altered.
复制

Note:
oracle 12c new feature: RESOURCE role without unlimited tablespace http://www.anbob.com/archives/2328.html

[oracle@db231 ~]$ sqlplus anbob/anbob@127.0.0.1/pdb12102
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 12 16:27:25 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
anbob@PDB12102>create table test(id int,note varchar2(4000) constraint chk_n check(note is json), article clob
constraint chk_a check(article is json));
Table created.
anbob@PDB12102>SELECT * FROM USER_JSON_COLUMNS;
TABLE_NAME COLUMN_NAME FORMAT DATA_TYPE
--------------- --------------- --------- -------------
TEST NOTE TEXT VARCHAR2
TEST ARTICLE TEXT CLOB
anbob@PDB12102>insert into test(id,note) values(1,'anbob.com');
insert into test(id,note) values(1,'anbob.com')
*
ERROR at line 1:
ORA-02290: check constraint (ANBOB.CHK_N) violated
anbob@PDB12102>insert into test(id,note) values(1,'{"name":"oracle", "db":{"version":"12","platform":"linux"}}');
1 row created.
anbob@PDB12102>col note for a60
anbob@PDB12102>select id,note from test;
ID NOTE
-------------------- ------------------------------------------------------------
1 {"name":"oracle", "db":{"version":"12","platform":"linux"}}
anbob@PDB12102>select json_query(NOTE,'$.db') from test;
JSON_QUERY(NOTE,'$.DB')
----------------------------------------------------------------------------------
{"version":"12","platform":"linux"}
anbob@PDB12102>select json_value(NOTE,'$.db.version') as v from test;
V
------------------------------------------------------------------------------------
12
anbob@PDB12102>select jtab.* from test, json_table(note,'$.db'
2 columns(row_number for ORDINALITY,
3 version_name varchar2(20) PATH '$.version',
4 pltf_name varchar2(20) PATH '$.platform')) as jtab;
ROW_NUMBER VERSION_NAME PLTF_NAME
-------------------- -------------------- --------------------
1 12 linux
复制

SQL Query to create JSON string
The following SQL query creates JSON for a company object with all data from DEPT and EMP (SCOTT Schema) as its contents:

	
with manager as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"salary":'||sal
       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, employee as
( select '{ '
       ||' "name":"'||ename||'"'
       ||',"job":"'||job||'"'
       ||',"salary":'||sal
       ||',"manager":'||case when mgr is null then '""' else (select json from manager mgr where mgr.empno = emp.mgr) end       ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
       ||'} ' json
  , emp.*
  from   emp
)
, department as
( select '{ '
       ||' "name":"'||dname||'"'
       ||',"identifier":"'||deptno||'"'
       ||',"location":"'||loc||'"'
       ||',"employees":'||(  select '['||listagg( json, ',')
                                                  within group (order by 1)
                                  ||']' as data
                             from employee emp
                             where emp.deptno = dept.deptno
                          )
       ||'} ' json
  from   dept
)
select '{"company" : ['
       ||( select listagg( json, ',')
                  within group (order by 1)
           from   department
          )
       ||']}'
from   dual;
复制

References http://www.liberidu.com/blog/2013/09/26/oracle-openworld-json-support-in-the-oracle-12c-database-upcoming-new-feature/