Oracle 12cR2 -Part1-table with json column overview

Oracle12开始支持JavaScript Object Notation (JSON) 数据,支持包括事务、索引、查询和视图等关系数据库的特性。
在Oracle 12.2版本下对json进行了很多增强,增加的特性请参考changes in Release 12.2
测试环境为Linux 86_64下的Oracle 12.2.0.1,测试脚本如下

drop table t_json purge;

create table t_json
(id varchar2(32) NOT NULL PRIMARY KEY,
data_echo_area CLOB
constraint data_echo_area_json check (data_echo_area is json)
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 100, 
    "first_name": "Steven", 
    "last_name": "King", 
    "email": "SKING",
    "job_id": "AD_PRES", 
    "salary": 24000.0, 
    "manager_id": null, 
    "department_id": 90,
    "address":{"city": "Oxford",
              "STATE_PROVINCE": "Oxford",
              "STREET_ADDRESS": "Magdalen Centre, The Oxford Science Park"
              }
   }'
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 101, 
    "first_name": "Neena", 
    "last_name": "Kochhar", 
    "email": "NKOCHHAR", 
    "job_id": "AD_VP", 
    "salary": 17000.0, 
    "manager_id": 100, 
    "department_id": 90,
    "address":{"city": "South Brunswick",
               "STATE_PROVINCE": "New Jersey",
               "STREET_ADDRESS": "2007 Zagora St"
               }
   }'
);
commit;

首先创建包含json类型的字段并添加check约束保证加载的数据都是符合json格式的数据,当然也可以不加这个约束,但是需要保证INSERT的数据都是完全符合json格式的数据。当存在约束并加载不符合json格式数据的时候会报ORA-2290错误。
生成json格式数据可以参考get json from table using python

查询json类型数据的时候使用dot标识,例如

SQL> col employee_id for a15
SQL> col LAST_NAME for a20
SQL> col DEPARTMENT_ID for a10
SQL> select t.id,t.data_echo_area.employee_id,t.data_echo_area.last_name,t.data_echo_area.department_id from T_JSON t;

ID                               EMPLOYEE_ID     LAST_NAME            DEPARTMENT
-------------------------------- --------------- -------------------- ----------
487897D071801468E0530B01A8C03972 100             King                 90
487897D071811468E0530B01A8C03972 101             Kochhar              90

当查询address这个not-scalar的数据的时候,返回的也是json格式的文本。

SQL> select t.id,t.data_echo_area.address from T_JSON t;

ID                               ADDRESS
-------------------------------- ------------------------------------------------------------
4878DB92BD00152AE0530B01A8C0C923 {"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":
                                 "Magdalen Centre, The Oxford Science Park"}

4878DB92BD01152AE0530B01A8C0C923 {"city":"South Brunswick","STATE_PROVINCE":"New Jersey","STR
                                 EET_ADDRESS":"2007 Zagora St"}

查询包含json数据类型的列的数据字典有DBA_JSON_COLUMNS, USER_JSON_COLUMNS和ALL_JSON_COLUMNS.
下面测试下json check constraint的几种形式和json相关视图的关系

SQL> col TABLE_NAME for a8
SQL> col COLUMN_NAME for a15
SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB


SQL> alter table T_JSON disable constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB

SQL> 
SQL> 
SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> 
SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json or length(data_echo_area) > 1000);

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json);
alter table t_json add constraint data_echo_area_json check (data_echo_area is json)
                                  *
ERROR at line 1:
ORA-02293: cannot validate (JSON.DATA_ECHO_AREA_JSON) - check constraint violated


SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json) novalidate;

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected


SQL>  insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));
 insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0))
*
ERROR at line 1:
ORA-02290: check constraint (JSON.DATA_ECHO_AREA_JSON) violated


SQL> insert into t_json(id,data_echo_area) values
  2  (sys_guid(),
  3  '{
  4      "employee_id": 101, 
  5      "first_name": "Neena", 
  6      "last_name": "Kochhar", 
  7      "email": "NKOCHHAR", 
  8      "job_id": "AD_VP", 
  9      "salary": 17000.0, 
 10      "manager_id": 100, 
 11      "department_id": 90,
 12      "address":{"city": "South Brunswick",
 13                 "STATE_PROVINCE": "New Jersey",
 14                 "STREET_ADDRESS": "2007 Zagora St"
 15                 }
 16     }'
 17  );

1 row created.

SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';
OWNER      TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
---------- -------- --------------- --------- -------------
JSON       T_JSON   DATA_ECHO_AREA  TEXT      CLOB

当disable掉check约束的时候,此时可以从json的数据字典表查看到jsob字段的信息;
当drop掉check约束的时候,json的数据字典视图中不再显示t_json表的json字段信息;
当增加json约束和其他的非json约束的时候,json的数据字典中不显示json的字段信息;
在constaint为novalidate的时候,非json格式的数据无法INSERT到存在json格式的表中,且此时json相关的数据字典中没有json字段信息;
当INSERT到表中json格式的数据后,json的数据字典信息中记录了json字段的信息。

此条目发表在12c, json, LOB分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s