Oracle如何实现JSON数据的新增、删除和修改

最近在项目开发过程中，遇到了一个棘手的问题。项目要求把大型的JSON数据存入数据库（存为clob类型），但由于数据内容太长，再加上Oracle版本的一些限制，好多常规的处理方法都用不了，属实让人头疼。不过别担心，经过一番摸索，我算是把这问题给整明白了。这篇文章就给大伙分享一下在Oracle里对JSON数据进行增删改的方法，要是你也被这问题困扰，希望能帮到你！

一、原始数据长啥样

咱先看看原始的JSON数据结构，就像下面这样：
'{"name": "李四", "skillLevel":{"项目经理": "高级","技术经理": "高级","产品经理": "高级"}}'
后续的操作都是基于这个数据来的。

二、具体需求都有啥

修改一级value：把“李四”改成“李四大佬” 。
修改二级value：将“项目经理”对应的“高级”修改为“超高级” 。
删除key：把“技术经理”这个属性删掉。
保持不变：“产品经理”就保持原样，不做任何改动。
新增key – value：添加一个“技术总监”属性，级别设置为“超高级”。

三、实现需求的SQL示例

直接上SQL代码，想要实现上面这些需求，用下面这条SQL就行：

SELECT JSON_MERGEPATCH( '{"name": "李四", "skillLevel":{"项目经理": "高级","技术经理": "高级","产品经理": "高级"}}' , '{"name": "李四大佬", "skillLevel":{"项目经理": "超高级","技术经理": null, "技术总监": "超高级"}}' RETURNING CLOB PRETTY ) AS text FROM dual t

这里解释一下，因为我的JSON数据特别大（超过了varchar2的存储限制），如果不指定返回clob类型，也就是加上RETURNING CLOB PRETTY，它就处理不了，会返回NULL。这个函数只有两个入参，第二个入参后面跟着指定的返回类型。

四、实际应用场景中的SQL

在实际项目里，比如要修改李四的信息，假设数据存在JSON_DATA_TABLE表中，字段是JSON_DATA，可以用下面这条SQL：

UPDATE T.JSON_DATA = JSON_MERGEPATCH( T.JSON_DATA , '{"name": "李四大佬", "skillLevel":{"项目经理": "超高级","技术经理": null, "技术总监": "超高级"}}' RETURNING CLOB PRETTY ) FROM JSON_DATA_TABLE t WHERE tJSON_EXISTS(t.JSON_DATA, '$.name ? (@ == "李四")');

这条SQL的意思就是，在JSON_DATA_TABLE表里找到JSON_DATA字段中name为“李四”的数据，然后用JSON_MERGEPATCH函数对这条数据进行修改。

五、其他相关函数介绍

在处理JSON数据时，除了JSON_MERGEPATCH函数，Oracle还有不少好用的函数，给大伙简单介绍一下：

JSON_QUERY：这个函数用来查询对象。要是查询的属性不是对象，那就返回NULL。比如下面这条SQL：

-- JSON_QUERY 查询对象，如果查询的属性不是对象，则返回 NULL SELECT JSON_QUERY('{"common": {"sex": { "M": "男", "F": "女", "U": "不詳" }}}' , '$.common.sex ') as sexLabel FROM DUAL;

JSON_VALUE：它是用来查询字段的。要是查询的属性是对象，就返回NULL。像这样：

-- JSON_VALUE 查询字段，如果查询的属性是对象，则返回 NULL SELECT JSON_VALUE('{"common": {"sex": { "M": "男", "F": "女", "U": "不詳" }}}' , '$.common.sex.M ') as sexLabel FROM DUAL;

JSON_EXISTS：可以用来判断属性或者值存不存在。看下面这俩例子：

-- 判断属性或者值是否存在 SELECT 1 FROM DUAL WHERE JSON_EXISTS('{"common": {"sex": { "M": "男", "F": "女" }}}' , '$.common.sex.M '); SELECT 1 FROM DUAL WHERE JSON_EXISTS('{"common": {"sex": { "M": "男", "F": "女" }}}' , '$.common.sex.M ? (@ == "男") ');

JSON_MERGEPATCH：前面已经用过了，它的作用就是合并两个对象，第二个参数里存在的属性会把第一个JSON对象里对应的属性给替换掉。比如：

-- 合并两个对象，将第二个参数的存在的属性替换掉第一个 JSON 对象的属性； SELECT JSON_MERGEPATCH('{"common": {"sex": { "M": "男", "F": "女" }}} ', '{"common": {"sex": { "M": "男1", "F": "女" }}} ') as sex FROM DUAL;

要是JSON_MERGEPATCH的返回值特别大，就得指定返回类型，像这样：

-- 如果 JSON_MERGEPATCH 的返回值超大，则需要指定返回类型： SELECT JSON_MERGEPATCH(TO_CLOB('{"common": {"sex": { "M": "男", "F": "女" }}}'), '{"common": {"sex": { "M": "男1", "F": "女" }}} ' RETURNING CLOB PRETTY ) as sex FROM DUAL;