oracle 12c新功能之JSON数据类型支持

oracle 12c提供了原生的json类型支持,json的数据可以存放在VARCHAR、CLOB、BLOB的数据类型中,查询的字段可以直接下探到json对象的属性,在做一些快速原型应用的时候可以非常灵活支持业务对象属性的变更。

1、创建包含json对象的表

CREATE TABLE t_workflow(
flow_id NUMBER,
flow_title VARCHAR2(1000),
flow_meta CLOB CONSTRAINT chk_flow_meta CHECK(flow_meta IS json)
);

2、插入一些测试数据

INSERT INTO t_workflow VALUES(
1,
'外网权限申请',
'{"create_by": "adam", "create_at": "2016-04-25", "approved_by": "george", "approved_at":"2016-04-26"}'
);

INSERT INTO t_workflow VALUES(
2,
'VPN账号申请',
'{"create_by": "adam", "create_at": "2016-04-20", "approved_by": "george", "approved_at":"2016-04-22"}'
);

BEGIN
FOR i IN 3..1002 LOOP
INSERT INTO t_workflow VALUES(
i,
'VPN账号申请' || i,
'{"create_by": "ben", "create_at": "2016-04-20", "approved_by": "john", "approved_at":"2016-04-22"}'
);
END LOOP;
COMMIT;
END;

INSERT INTO t_workflow VALUES(
10000,
'VPN账号申请',
'{"create_by": "adam", "create_at": "2016-04-20", "approved_by": "george", "approved_at":"2016-04-22", "user_name": "adam"}'
);
INSERT INTO t_workflow VALUES(
10002,
'VPN账号申请-frank',
'{"create_by": "frank", "create_at": "2016-04-26", "approved_by": "george", "approved_at":"2016-04-27", "user_name": "adam"}'
);

3、查询json对象及其属性,json对象的值也可以作为查询条件

SELECT
flow_id,
flow_title,
t.flow_meta.create_by,
t.flow_meta.create_at,
t.flow_meta.approved_by,
t.flow_meta.approved_at,
t.flow_meta.user_name
FROM t_workflow t
WHERE t.flow_meta.create_by = 'adam';

上面这种写法,必须使用表的别名,才能引用json对象的属性。
也可以使用json_value的函数

SELECT
flow_id,
flow_title,
json_value(flow_meta, '$.create_at') create_at,
json_value(flow_meta, '$.create_by') create_by,
json_value(flow_meta, '$.approved_at') approved_at,
json_value(flow_meta, '$.approved_by') approved_by,
json_value(flow_meta, '$.user_namme') user_namme
FROM t_workflow t
WHERE t.flow_meta.create_by = 'adam'

4、索引创建在json对象的属性上,本质上是一个函数索引

CREATE INDEX ind_workflow1 ON t_workflow t(t.flow_meta.create_by);

从上一步查询语句的执行计划,可以看到查询中确实使用了索引

Execution Plan
----------------------------------------------------------
Plan hash value: 1929962788
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |   960 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_WORKFLOW    |     3 |   960 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_WORKFLOW1 |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_QUERY("FLOW_META" FORMAT JSON , '$.create_by' RETURNING VARCHAR2(4000)
ASIS  WITHOUT ARRAY WRAPPER NULL ON ERROR)='adam')

5、使用json_exists函数,判断json对象是否存在某个属性,使用json_query返回整个json对象

SELECT
flow_id,
flow_title,
json_query(flow_meta, '$')
FROM t_workflow t
WHERE json_exists(flow_meta, '$.user_name');

6、如果json中含有数组类型,可以使用json_table函数展开,可以其他列的数据做join操作。

CREATE TABLE t_article(
article_id NUMBER,
article_title VARCHAR2(1000),
article_comments CLOB CONSTRAINT chk_comments CHECK(article_comments IS json)
);
INSERT INTO t_article VALUES (
1,
'oracle 12c新特性',
'[{"comment_by": "adam", "comment_content": "TOM大神的好书"}]'
);

INSERT INTO t_article VALUES (
2,
'PL/SQL揭秘',
'[{"comment_by": "frank", "comment_content": "值得一读"},{"comment_by": "john", "comment_content": "适合有经验的开发者"}]'
)
commit;

上面article_id=2的数据,article_comments是个数组,包含两个comment的json对象,使用json_table展开后,article_id=2的行在结果集中会变为2行,每行一个comment对象对应的属性。

SELECT
  article_id, article_title,
  jt.comment_by, jt.comment_content
FROM t_article,
json_table(article_comments, '$[*]'
  columns(row_number for ordinality,
          comment_by varchar2(20) path '$.comment_by',
          comment_content varchar2(200) path '$.comment_content'))
as jt;
ARTICLE_ID ARTICLE_TITLE COMMENT_BY COMMENT_CONTENT
1 oracle 12c新特性 adam TOM大神的好书
2 PL/SQL揭秘 frank 值得一读
2 PL/SQL揭秘 john 适合有经验的开发者

7、查询哪些列使用了json类型

SELECT * FROM User_Json_Columns;

参考资料:
The new SQL/JSON Query operators (Part1: JSON_VALUE)
The new SQL/JSON Query operators (Part2: JSON_QUERY)
The new SQL/JSON Query operators (Part3: JSON_EXISTS)
The new SQL/JSON Query operators (Part4: JSON_TABLE)
The new SQL/JSON Query operators (Part5: JSON_TABLE, Nested Path, Ordinality Column)

Comments are closed.