oracle12C数据库JSON的应用 --PL/SQL使用json简单入门篇

太过爱你忘了你带给我的痛 2022-05-17 06:00 1463阅读 0赞

**最近项目在后端与数据库方面的交互中涉及到JSON，并且oracl12以上的版本支持JSON格式的数据存储和解析；本文章就先从简单入手，从入门学习ORACLE 12C中的JSON应用，后面再上传关于JSON在数据库方面的更深层次的应用，如存储过程等等方面的应用。**

**更深层次的应用请点击此处：[pl/sql存储过程中json解析应用篇][pl_sql_json]**

**一、oracle12.2.0.1版本增加的json-data功能特性：**

1.对JSON Data的存储和管理

(1)数据表可以使用JSON 虚拟列来作表的键key

(2)可以创建JSON索引(with range,list, hash, or interval partitioning)

2.对JSON Data的查询

（1）JSON路径表达式增强功能：可以包含必须满足的过滤器表达式，通过匹配的数据和转换方法来改变它。

（2）解析成JSON后可以使用点符号访问JSON数组或对象中的元素

（3）增加JSON functions(方法):

> json\_value, json\_query, json\_object, json\_array, json\_table, json\_objectagg, json\_arrayagg

JSON condition(条件判断):

> json\_exists(返回boolean数据类型), is json, is not json, json\_textcontains

3.还有对一些搜索索引的性能增强，视图中使用json数据等等

**二、实用的使用案例(PL/SQL的json\_value值支持返回boolean的数据类型)：**

在数据库中使用JSON的一般用法：

（1）在create table时，添加json字段column,并CHECK 该字段CONSTRAINT IS JSON数据（即是说明该字段列存是JSON数据，每次insert该字段列数据都会去CHECK一下是否 IS JSON）,JSON字段类型可以是VARCHAR2,CLOB（字符大对象） or BLOB（二进制大对象）,字节大小VARCHAR2<CLOB、BLOB，PS建议，使用varchar2(4000)大小不超4000字节可以提高性能（最大为32767字节），超过建议使用下一行存数据，若执着超过32767字节则使用CLOB、BLOB

例如：

> CREATE TABLE c\_json\_t
> 
> (id VARCHAR2(35) NOT NULL PRIMARY KEY,
> 
> \----定义json\_data为JSON数据类型,(STRICT)为定义严格的JSON格式
> 
> json\_data VARCHAR2(4000) CONSTRAINT ensure\_jsondata CHECK (json\_data IS JSON(STRICT)));

（2）将json数据insert到JSON字段colum中,可以进行多嵌套JSONObject和JSONArray格式的JSON数据

例如：

> INSERT INTO c\_json\_t
> 
> VALUES(
> 
> SYS\_GUID(),
> 
> '\{ 
> 
> "Name":"ZhangSan",
> 
> "Sex":null,
> 
> "SNO":"98565",
> 
> "Subject":\{"kind":"arts","name":\{"1":"Chinese","2":"English"\}\},
> 
> "grede":\[\{"name":"Chinese","sore":"65"\},\{"name":"English","sore":"56"\}\]
> 
> \}');

(3) Query查询JSON

1）可以使用条件：json\_exists检查是否存在某个json值，

is json和is not json检查是否包含json数据；

2）可以使用方法：

json\_value可以选取其他的json数据作为当前sql的某个字段的value值，json\_query可以从其他的json数据查询一个或者多个values（通常是jsonObject或jsonArray）作为当前sql的某个json字段的json数据字符串，json\_table可以将json数据作为一个虚拟表

例如：

> \----查询json\_exists检查
> 
> select t1.json\_data.Name from c\_json\_t t1 where json\_data is json and json\_exists(json\_data,'$.Subject.name');
> 
> \----查询json key中subkey对应的value值
> 
> select t1.json\_data.Subject.name from c\_json\_t t1;
> 
> \----查询json\_value
> 
> SELECT \* FROM c\_json\_t where id=json\_value(json\_data,'$.SNO');
> 
> \----查询json\_query
> 
> SELECT json\_query(column,json\_path RETURNING data\_type array\_wrapper error\_hander ---ON ERROR) FROM table;
> 
> SELECT json\_query(json\_data,'$.Name' with WRAPPER ) FROM c\_json\_t
> 
> \----json\_table
> 
> SELECT jt.column\_alias FROM table,json\_table(column,'$' error\_handler ON ERROR COLUMNS ("COLUMN\_ALIAS" data\_type FORMAT JSON array\_wrapper PATH json\_path
> 
> ))AS "JT";

[pl_sql_json]: https://blog.csdn.net/use_admin/article/details/82894944