oracle11 linux安装包（oracle中对JSON数据处理）
oracle中对JSON数据处理
背景
在公司接手的项目碰到这么一个表 ，里面有一列数据保存的是JSON字符串 。如果说这一列是一些配置数据 ，不需要应用到筛选和过滤 ，只是跟着主键去获得的数据 ，那你这么做并没有什么不妥 。但事实是 ，这一列需要检索和过滤数据 ，这**居然是遍历全表 ，再把json数据转换成对象去遍历匹配 。我服了 ，你咋这么能呢？喜欢这样玩为什么当初不用MongoDB？查了一下文档 ，知道Oracle 在12C之后有对JSON数据的操作方法 ，网上关于这部分的文章非常非常非常少（毕竟没几个人会把oracle当成MongoDB这么地**是吧） ，就整理一下 ，方便自己以后查看 。
入门探究
有一说一这官方文档非常地硬 ，啃了好久都啃不动 ，而且很多理想当然的用法都不行 。Oracle中关于JSON的操作函数有JSON_ARRAY 、JSON_EXISTS 、JSON_VALUES 、JSON_TABLE 、JSON_QUERY ，其中JSON_ARRAY是用来构建JSON数组的 ，而不是用来查找结果的 。在文中也会小篇幅简单说一下用法 。
JSON_EXISTS
JSON_EXISTS是一个SQL函数 ，用于判断是否存在符合条件的JSON数据 。它可以在WHERE子句中使用 ，用于筛选JSON列中符合特定条件的数据 。
官方给出的语法是这样的：
JSON_EXISTS(json_column, json_path_expression, [returning_clause])其中 ，json_column是指要查询的JSON列 ，json_path_expression是指JSON路径表达式 ，用于指定要查找的JSON数据的位置和条件 。
JSON路径表达式中可以使用各种操作符和函数 ，以及通配符和其他特殊字符 。例如：
$: 表示根元素 .: 表示当前元素 [*]: 表示匹配数组中的所有元素 []: 表示过滤数组中符合条件的元素 @: 表示当前元素的属性在json_path_expression中 ，可以使用一些函数 ，如JSON_VALUE 、JSON_QUERY 、JSON_TABLE等来处理JSON数据 。例如：
JSON_VALUE(json_column, $.name): 返回json_column列中name字段的值 JSON_QUERY(json_column, $.name): 返回json_column列中address字段的值 JSON_TABLE(json_column, $.hobbies[*] COLUMNS (phone_number VARCHAR2(20) PATH $)): 返回json_column列中phones数组中的所有元素的phone_number字段的值返回值：
如果符合条件的JSON数据存在 ，则返回TRUE；否则返回FALSE 。如果使用returning_clause子句 ，则返回符合条件的JSON数据 。
例如：
SELECT * FROM my_table WHERE JSON_EXISTS(json_data, $.name);这个例子查询my_table表中json_data列中是否存在name字段的值 。如果存在 ，返回TRUE ，否则返回FALSE 。
JSON_ARRAY
JSON_ARRAY 是一个 Oracle SQL 函数 ，用于创建一个 JSON 数组 。下面是使用 JSON_ARRAY 的一些示例：
语法：
JSON_ARRAY(value1 [, value2] [, value3] ...);其中 ，value1, value2, value3 等是一个或多个要添加到 JSON 数组的值 。这些值可以是任何有效的 SQL 值 ，例如字符串 、数字 、日期 、布尔值等 。如果没有指定任何值 ，JSON_ARRAY 将生成一个空的 JSON 数组 。
创建一个包含两个字符串值的 JSON 数组
SELECT JSON_ARRAY(apple, orange) FROM dual;输出结果：
["apple", "orange"]创建一个包含两个数字值的 JSON 数组
SELECT JSON_ARRAY(10, 20) FROM dual;输出结果：
[10, 20]创建一个包含多个元素的 JSON 数组
SELECT JSON_ARRAY(apple, 10, TRUE) FROM dual;输出结果：
["apple", 10, true]在上述示例中 ，JSON_ARRAY 函数接受一个或多个参数 ，并将它们作为一个 JSON 数组返回 。你可以在 JSON_ARRAY 中使用不同类型的参数 ，例如字符串 ，数字和布尔值 ，它们都将被转换为相应的 JSON 类型 。
JSON_VALUE
JSON_VALUE函数用于提取JSON文档中的值 。它的语法如下：
JSON_VALUE(json_document, path_expression [RETURNING datatype])其中：
json_document 是包含JSON文档的字符串或BLOB列 。 path_expression 是用于指定要提取的值的路径表达式 。 datatype 是可选的 ，用于指定返回值的数据类型 。以下是一些示例：
提取JSON文档中的单个值：
SELECT JSON_VALUE({"name": "John", "age": 30}, $.name) as name FROM dual;输出：
NAME ---- John提取JSON文档中的数组：
SELECT JSON_VALUE({"fruits": ["apple", "banana", "orange"]}, $.fruits) as fruits FROM dual;输出：
FRUITS ----------------------- ["apple", "banana", "orange"]提取JSON文档中的数组元素：
SELECT JSON_VALUE({"fruits": ["apple", "banana", "orange"]}, $.fruits[0]) as first_fruit FROM dual;输出：
FIRST_FRUIT ----------- apple指定返回值的数据类型：
SELECT JSON_VALUE({"price": 9.99}, $.price RETURNING NUMBER) as price FROM dual;输出：
PRICE ----- 9.99在使用JSON_VALUE函数时 ，需要注意以下几点：
如果路径表达式不匹配JSON文档中的任何内容 ，则返回NULL 。 如果未指定返回值的数据类型 ，则返回的值将是一个字符串 。 JSON_VALUE函数还有一个类似的兄弟函数JSON_QUERY ，不同的是 ，它返回JSON对象或数组 ，而不是标量值 。JSON_QUERY
JSON_QUERY函数用于从JSON文档中查询数据 ，返回一个JSON对象或数组 。它的语法如下：
JSON_QUERY(json_document, path_expression [RETURNING datatype])其中：
json_document 是包含JSON文档的字符串或BLOB列 。 path_expression 是用于指定要查询的值的路径表达式 。 datatype 是可选的 ，用于指定返回值的数据类型 。以下是一些示例：
查询JSON文档中的单个值： SELECT JSON_QUERY({"name": "John", "age": 30}, $.name) as name FROM dual;输出：
NAME ---- null 查询JSON文档中的数组： SELECT JSON_QUERY({"fruits": ["apple", "banana", "orange"]}, $.fruits) as fruits FROM dual;输出：
FRUITS ----------------------- ["apple", "banana", "orange"] 查询JSON文档中的数组元素： SELECT JSON_QUERY({"fruits": ["apple", "banana", "orange"]}, $.fruits[0]) as first_fruit FROM dual;输出：
FIRST_FRUIT ----------- null因为是单值 ，json_query无法展示
在使用JSON_QUERY函数时 ，需要注意以下几点：
如果路径表达式不匹配JSON文档中的任何内容 ，则返回NULL 。 如果未指定返回值的数据类型 ，则返回的值将是一个字符串 。 JSON_QUERY函数还有一个类似的兄弟函数JSON_VALUE ，不同的是 ，它返回标量值 ，而不是JSON对象或数组 。总之 ，JSON_QUERY函数在处理JSON文档时非常有用 ，可以轻松地查询和提取需要的数据 。
JSON_TABLE
JSON_TABLE函数可以将JSON数据转换为表格形式 。以下是JSON_TABLE的用法：
语法：
JSON_TABLE(json, path COLUMNS (column1 expr1 [, column2 expr2]...))参数说明：
json：要转换的JSON数据 。 path：要提取的JSON元素的路径 。 COLUMNS：指定要转换的列及其表达式 。 column1 expr1 [, column2 expr2]...：指定要转换的列及其表达式 。示例：
假设我们有以下JSON数据：
{ "employees": [ { "name": "John", "age": 30, "gender": "male" }, { "name": "Jane", "age": 25, "gender": "female" }, { "name": "Bob", "age": 35, "gender": "male" } ] }我们可以使用以下查询将其转换为表格形式：
SELECT name, age, gender FROM JSON_TABLE({ "employees": [ { "name": "John", "age": 30, "gender": "male" }, { "name": "Jane", "age": 25, "gender": "female" }, { "name": "Bob", "age": 35, "gender": "male" } ] }, $.employees[*] COLUMNS ( name VARCHAR2(50) PATH $.name, age NUMBER PATH $.age, gender VARCHAR2(10) PATH $.gender ));输出结果：
NAME AGE GENDER ------ ---- ------ John 30 male Jane 25 female Bob 35 male在上面的例子中 ，$.employees[*]指定要处理的JSON元素路径 ，name, age, 和 gender 列使用 PATH 关键字指定表达式的路径 。
进阶实战篇
我举个实际的例子 ，我有一列 ，对应的一列的数据大概是这样的：
{ "name": "cxk", // string "sex": "female", // stirng "hobbies": ["sing", "dance", "rap", "basketball"], // 普通数组 "company": {"name": "unknow", "staffNum": "unknow"}, // 对象属性 "fans": [ // 对象数组 {"name": "ncFans1"}, {"name": "ncFans2"} ] }1. JSON_QUERY与JSON_VALUE的区别
看了前面的介绍 ，可能有些同学对这个完全不能理解 ，有什么区别？
查询单值 select JSON_QUERY ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.name ) as json_query_res, JSON_VALUE ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.name ) as json_value_res from dual;输出结果：
JSON_QUERY_RES JSON_VALUE_RES -------------------------------- null cxk区别1：json_query无法返回单值 ，json_value可以返回单值
查询对象 select JSON_QUERY ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.company ) as json_query_res, JSON_VALUE ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.company ) as json_value_res from dual;输出结果：
JSON_QUERY_RES JSON_VALUE_RES -------------------------------- ------------------ {"name": "unknow", "staffNum": "unknow"} null区别2：json_query能返回对象 ，json_value不能返回对象值
查询普通数组 select JSON_QUERY ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.hobbies ) as json_query_res, JSON_VALUE ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.hobbies ) as json_value_res from dual;输出结果：
JSON_QUERY_RES JSON_VALUE_RES -------------------------------- ------------------ ["sing", "dance", "rap", "basketball"] null区别3：json_query能返回普通数组 ，json_value不能返回数组
结合1 、2 、3点 ，我们给hobbies加个下标 select JSON_QUERY ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.hobbies[0] ) as json_query_res, JSON_VALUE ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.hobbies[0] ) as json_value_res from dual;输出结果：
JSON_QUERY_RES JSON_VALUE_RES -------------------------------- ------------------ null sing 查询对象数组 select JSON_QUERY ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.fans ) as json_query_res, JSON_VALUE ({ "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"}, "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] }, $.fans ) as json_value_res from dual;输出结果：
JSON_QUERY_RES JSON_VALUE_RES -------------------------------- ------------------ [{"name": "ncFans1"},{"name": "ncFans2"}] null区别4：JSON_QUERY可以匹配对象数组的值 ，JSON_VALUE不可以
2. JSON_EXISTS判断某个值是否等于目标值
这是比较难的一个步骤 ，我自己摸索了很久很久才懂 ，我这里举例都写到了谓动词的位置 ，实际上JSON_EXISTS是用在where后的 。
案例1：找出存在name属性的行 SELECT case when JSON_EXISTS({"name": "John", "age": 30, "city": "New York"}, $.name) then true else false end as result FROM dual;输出：
RESULT --------- true但其实 ，我们更多都是在匹配name值等于具体的值 ，这就要用匹配的机制 ，但愿你还记得前面介绍的用法
SELECT case when JSON_EXISTS( { "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] } , $.name ? (@ == "cxk")) then true else false end as result FROM dual;@表示当前name属性这一层 ，值一定要用双引号括起来
多值匹配 SELECT case when JSON_EXISTS( { "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] } , $.company ? (@.name == "unknow" && @.staffNum == "unknow")) then true else false end as result FROM dual; 如果是不同层级的多值匹配 ，建议在where后用AND连接起来 ，比如： select * from mytable where JSON_EXISTS(my_cloumn, $.name ? (@ == "cxk")) AND JSON_EXISTS(my_cloumn, $.company ? (@.name == "unknow" && @.staffNum == "unknow")) JSON_EXISTS可以检索普通数组中的值 SELECT case when JSON_EXISTS( { "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] } , $.hobbies[*] ? (@ == "sing")) then true else false end as result FROM dual;执行结果为true
JSON_EXISTS无法检索对象数组 SELECT case when JSON_EXISTS( { "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [ {"name": "ncFans1"}, {"name": "ncFans2"} ] } , $.fans")) then true else false end as result FROM dual;无论是$.fans还是$.fans[*]返回结果都是false ，完全无法获取对象数组中的值 。无法判断里面的值的对比情况 。
JSON_EXISTS遇到空数组时 ，返回的是true SELECT case when JSON_EXISTS( { "name": "cxk", "sex": "female", "hobbies": ["sing", "dance", "rap", "basketball"], "company": {"name": "unknow", "staffNum": "unknow"} "fans": [] } , $.fans")) then true else false end as result FROM dual;但如果是$.fans[0]返回的是false
3. $.xx中不能用字符串拼接 、不能用任何函数会报ORA-40442错误
4. 比较非对象数组的值除了JSON_EXISTS可以用JSON_VALUE与JSON_QUERY
select * from mytable where JSON_VALUE(my_cloumn, $.name) = cxk and JSON_QUERY(my_cloumn, $.fans) = [] -- 判空5. JSON_QUERY不支持通配符 ，查询全部就是$.fans ，不需要$.fans[*]；支持使用下标指定
6. JSON_VALUE与JSON_QUERY可以嵌套使用 ，比如
SELECT * from mytable where JSON_VALUE(JSON_QUERY(my_cloumn, $.fans), $.name) = ncFans1这个语句就是查找出fans中有ncFans1的记录
7. 数组判空
使用JSON_QUERY select * from mytable where JSON_QUERY(my_cloumn, $.fans) = [] 使用JSON_EXISTS select * from mytable where not JSON_EXISTS(my_cloumn, $.fans[0])8. 使用JSON_TABLE可以解决任何复杂的问题 ，但是JSON_TABLE本身就很复杂 ，如果用来筛选数据 ，那这个语句太难维护了 。
9. 在mybatis框架中 ，由于$.xx的语句要放在单引号中 ，切不能使用任何函数 ，不能字符拼接 ，所以用#{}无法注入 ，要用${} ，这在有些公司是不被允许的 。目前我是这么用了 ，等安全部门的渗透测试结果出来 ，是否有sql注入风险再来更新 。
注意事项
以上提到的操作均需要在Oracle12以上版本中使用 ，但并不是12以上版本都能使用 。这个和数据库设置的compatible值有关 。compatible必须大于12.0
才可以使用 。
你可以使用拥有DBA权限的用户执行以下语句查看compatible值 SELECT name, value FROM v$parameter WHERE name = compatible否则 ，你会得到这样的一个报错
ORA-00406: COMPATIBLE parameter needs to be 12.0 or greater
然后你就白忙活了 ，因为一般都不会为了你特地升级compatible的 。没事 ，我就是白忙活了 。创心域SEO版权声明：以上内容作者已申请原创保护，未经允许不得转载，侵权必究！授权事宜、对本内容有异议或投诉，敬请联系网站管理员，我们将尽快回复您，谢谢合作！