很久前在国外的一个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/