ANBOB™

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

打赏

微信扫一扫，打赏作者吧～