Using JSON in Oracle 12c

Transcription

1 在 Oracle 12c 使用 JSON 演讲人 : John Jay King 下载地址 : 1

2 会议宗旨 理解 Oracle 12c 如何支持 JSON 在数据库中存储 JSON 数据 熟悉 JSON 的条件和功能 使用 JSON 条件和功能查询 JSON 数据 在 JSON_TABLE 上使用 SQL 查询 JSON 数据 2

3 个人简介 John King King Training Resources 的股东 Oracle Ace 总监 Oak Table Network 成员 给 Oracle 和 IT 社团提供培训, 有超过 25 年的工作经验, 网址是 对 Oracle, ADF, SQL, Java, 和 PL/SQL( 还有一些其他课题 ) 了如指掌的 技术专家 AZORA, ODTUG, IOUG, 和 RMOUG 的成员 3

4 何为 JSON JSON (JavaScript Object Notation) 是一种独立于语言, 开放式的 ( 在名值对中储存文本 最初出现在 JavaScript, 现在也出现于 : Java, R,.NET, PHP, Python, Node.js, 和 Oracle 中 常用于数据互换 频繁用于在 REST-style 网络服务中来回传递数据 因大数据的持久性而广受欢迎 4

5 12c ( ) 和 JSON 12c patch-set 2 ( ) 添加了 JSON JSON 文件以 VARCHAR2, CLOB, 或 BLOB 数据类型进行存储 JSON 数据可以和 Oracle 现有包括 SQL 和 Analytics 的所有特性一起工作 12c 支持 JSON data 基于路径的查询, 这些数据存储在数据库, JSON Path Language, 和 JSON Path Expressions 中 通过 SQL/JSON 视图, 在 SQL 中使用 JSON JSON 文件可以被索引 5

6 JSON-XML 相似性 JSON 就像 XML 格式一样, 只是文本, 因此它是进行数据互换的一种有效媒介 JSON 和 XML 是 人们可读的 并且能够进行 自描述 ( 有点 ) JSON 和 XML 是分层的 ( 数据集合安置在数据集内 ) JSON 和 XML 提供了验证功能 ; 现在 XML 的功能愈加成熟 6

7 JSON-XML 差异性 XML 比较冗长, JSON 相对更精简 JSON 没有结束标记, 而 XML 中则需要有结束标记 JSON 在读写速度方面更快 读取 XML 文件时需要 遍历 DOM JSON 则不需要 与 AJAX 一起运行时, 相对 XML 来说,JSON 运行起来更加便捷快速 在处理数据之前,XML 文件必须进行 语法规则 测试 7

8 JSON 句法 使用逗点隔开的名 / 值对来存储数据 字段名 / 键 ( 由双引号括起来 ) 冒号 :, 值 ( 由双引号括起的字符串, 不需要括起来的数值, 布尔值 trur/false/null 对象包含在大括号内 { & } {"lastname":"king"} 数组使用方括号 [ & ] 和逗号 [ {"lastname": "King"}, {"lastname": "Manzo"} ] 8

9 <?xml version="1.0"?> <mybooks> <book> <name>learning XML</name> <author>eric T. Ray</author> XML 文件 <publisher>o'reilly</publisher> </book> <book> <name>xml Bible</name> <author>elliotte Rusty Harold</author> <publisher>idg Books</publisher> </book> <book> <name>xml by Example</name> <author>sean McGrath</author> </book> </mybooks> 9

10 JSON 文件 {"mybooks": [ {"book": { "name":"learning XML", "author":"eric T. Ray", "publisher":"o'reilly" } }, {"book": { "name":"xml Bible", "author":"elliotte Rusty Harold", "publisher":"idg Books" } }, {"book": { "name":"xml by Example", "author":"sean McGrath", "publisher":"prentice-hall" } } ]} 10

11 Oracle 作为 JSON 数据存储 JSON 文件使用现有的数据类型存储到数据库中 VARCHAR2, CLOB 和 BLOB 针对 JSON 字符模式 外部的 JSON 数据资源 ( 包括 HDFS 文件系统中的那些资源 ) 可以通过外部表来获取 基于 JSON_TABLE 的相关视图, 使用 SQL 来获取 JSON 数据 Oracle JSON 文件可以被索引 ; JSON 路径可以使用功能索引 11

12 JSON SQL JSON 内容可以从 SQL 中获取, 而且可以视为 JSON 来使用 : JSON 条件运算符 JSON 功能 JSON 运算符和功能函数使用 JSON Path 语言来引导 JSON 对象 12

13 JSON 运算符 JSON 内容可以经过新的条件运算符从 SQL 中 IS JSON 验证 JSON, 通常是在 CHECK 限制和 WHERE 中 (IS / IS NOT) JSON_EXISTS 如果 JSON 路径在文件中存在则为 True JSON_TEXTCONTAINS 如果在 JSON 的属性值中发现文本字符串则为 True ( 使用 Oracle 文本 ) 13

14 JSON 功能 JSON 内容也可以通过新的功能来获取 JSON_VALUE 用来从 JSON 文件中查询标量值 JSON_QUERY 用来查询所有或者部分 JSON 文件内容 JSON_TABLE 用来查询 JSON 文件并且创建相关格式的列 14

15 IS JSON IS NOT JSON 如果特定的表达是一个有效的 JSON 文件, 那么 IS JSON 会返回 TRUE **expr** IS JSON IS NOT JSON FORMAT JSON STRICT LAX WITH WITHOUT UNIQUE KEYS 如果是有效的 JSON, 则为 IS true, 如果不是 JSON, 则为 IS NOT true FORMAT JSON ( 只是满足 BLOB 数据的需要 ) STRICT / LAX 严格检查级别 如果需要主键, 则为 WITH / WITHOUT 15

16 create table deptj JSON Check 有约束嘛 (id raw(16) not null, dept_info clob constraint deptjson check (dept_info is json) ); create table deptj (id raw(16) not null, dept_info clob constraint deptjson check (dept_info is json strict) ); 16

17 select id,dept_info from deptj where dept_info is json IS JSON in WHERE select id,dept_info from deptj where dept_info is json strict; select id,dept_info from deptj where dept_info is json format json strict; 17

18 insert into deptj values (sys_guid(), '{"departments":{ JSON 和 DML "DEPTNO": 10, "DNAME": "ACCOUNTING", "LOC": "NEW YORK", "deptemps": [ { "EMPNO": 7782, "ENAME": "CLARK", "JOB": "MANAGER", "MGR": 7839, "HIREDATE": "09-JUN-81", "pay":{ "SAL": 2450, "DEPTNO": "10" }, /* more */ "COMM": null}, 18

19 简单的 JSON Query select dept_info from deptj; DEPT_INFO {"departments":{ "DEPTNO": 10, "DNAME": "ACCOUNTING", "LOC": "NEW YORK", "deptemps": [ { "EMPNO": 7782, "ENAME": "CLARK", **** more **** 19

20 JSON 路径 JSON 文件中的 $.jsonpath Identifies 路径 $. ( 需要的 ) Jsonpath ( 如果不需要整个文件的返回, 那么就指定查找 JSON 文件中的部分来进行搜寻 ) 由 JSON_QUERY, JSON_VALUE, JSON_TABLE, JSON_EXISTS, JSON_TEXTCONTAINS 使用的 JSON path 20

21 JSON_VALUE JSON_VALUE 在 JSON 文件中发现了一个值, 并将它作为 VARCHAR2 或者 NUMBER 返回给 SQL JSON_VALUE (expr expr FORMAT JSON, '$.jsonpath RETURNING VARCHAR2(n) NUMBER(n,n) ERROR NULL DEFAULT xxx ON ERROR ) 21

22 使用 JSON_VALUE 查询 select json_value(dept_info, '$.departments.dname') from deptj; DNAME ACCOUNTING RESEARCH SALES OPERATIONS 22

23 JSON_VALUE JSON_VALUE 从 JSON 数据中返回标量值 select json_value(dept_info, '$.departments.dname') from deptj; select json_value(dept_info, '$.departments.deptemps[0].ename') from deptj; select dept_info from deptj where json_value(dept_info, '$[0].departments.DEPTNO') = '10' 23

24 JSON_TABLE JSON_TABLE 把 JSON 数据匹配到相关的行和列中 JSON_TABLE ( expr expr FORMAT JSON,'$.jsonpath ERROR NULL DEFAULT xxx ON ERROR COLUMNS ( colname1 datatype EXISTS PATH '$.jsonpath ERROR NULL DEFAULT xxx ON ERROR, colname2 datatype FORMAT JSON jsonquerywrapper PATH '$.jsonpath ERROR NULL DEFAULT xxx ON ERROR, colname3 datatype PATH '$.jsonpath ERROR NULL DEFAULT xxx ON ERROR, NESTED PATH '$.jsonpath' COLUMNS (...), colname4 FOR ORDINALITY ) 24

25 使用 JSON_TABLE 查询 select dname,ename,job,sal from deptj, json_table(dept_info,'$.departments' columns (dname varchar2(15) path '$.DNAME',nested path '$.deptemps[*]' columns (ename varchar2(20) path '$.ENAME',job varchar2(20) path '$.JOB',nested path '$.pay' columns (sal number path '$.SAL') ) )); DNAME ENAME JOB SAL ACCOUNTING CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 **** more **** 25

26 结束演讲 Oracle 12c 及时的对 JSON 进行了支持, 并非常有用 相信很快 JSON 就会被得到应用 JSON 是 : 与 AJAX 相互的一种最常见的机制 成为网络服务数据最常见的机制 ( 尤其是基于服务的 REST/HTTP API ) 几种 大数据 数据存储的基石 花费时间来了解 JSON, JSON 句法, 和 Oracle 的实现 26

27 RMOUG Training Days 年 2 月 9-11 日 ( 周二 - 周四 ) 丹佛会展中心 R 27

28 COLLABORATE 16 IOUG 论 坛,2016 年 4 月 日 Mandalay Bay Las Vegas, NV 28

29 29

30 请完成会议评估 在 Oracle 12c 中使用 JSON 联系作者 : John King King Training Resources P. O. Box 1780 Scottsdale, AZ USA [email protected] 感谢收听 可在该网站查看今天的幻灯片和例子 : 30

31 结束 31