Oracle :选择查询:搜索是否clob包含具有模式匹配的字符串
我有一个包含JSON数据的CLOB列的可检测表。我需要一个查询，它将在CLOB数据中搜索。
我使用了条件where DBMS_LOB.instr(colName,'apple:')>0，它给出了具有apple:的记录。但是，我需要查询来返回除空白之外的任意数量的苹果记录，这意味着json apple键应该有一个值。
我想到的是类似于where DBMS_LOB.instr(colName,'apple:**X**')>0的东西，其中X可以是任何数字而不是null。我尝试过regexp_instr，但这似乎不适合CLOB。
有什么办法解决这个问题吗？
回答 2
Stack Overflow用户
发布于 2021-04-12 17:38:42
用于解析JSON输入的泛型字符串函数是危险的--例如，当看起来像JSON对象的东西实际上嵌入到字符串值中时，您将得到假阳性。(下面的例子中ID = 101说明了这一点。)
理想的情况是您使用的是Oracle 19或更高版本；在这种情况下，您可以使用对json_exists的简单调用，如下所示。在我创建的示例表中，第一个JSON字符串不包含名为apple的成员。在第二行中，字符串确实包含一个成员apple，但值是null。我显示的第一个查询(查找带有apple成员的所有JSON )将在输出中包含此行。最后一个查询是您所需要的:它添加了一个过滤器，以便JSON字符串必须包含至少一个具有非null值的null成员(不管它是否还包括其他名为apple的成员，可能包含null值)。
create table sample_data
( id number primary key
, colname clob check (colname is json)
);
insert into sample_data
values (101, '{name:"Chen", age:83, values:["{apple:6}", "street"]}');
insert into sample_data
values (102, '{data: {fruits: [{orange:33}, {apple:null}, {plum:44}]}}');
insert into sample_data
values (103, '[{po:3, "prods":[{"apple":4}, {"banana":null}]},
{po:4, "prods":null}]');请注意，我有意将引用的和未引用的成员名称混合在一起，以验证下面的查询在所有情况下都正确工作。(还请记住，JSON中的成员名是大小写敏感的，甚至在Oracle中也是如此！)
select id
from sample_data
where json_exists(colname, '$..apple')
;
ID
---
102
103这是您需要的查询。注意路径中的.. (意思是--在JSON中的任何地方找到一个名为apple的对象成员)和在末尾的过滤器。
select id
from sample_data
where json_exists(colname, '$..apple?(@ != null)')
;
ID
---
103Stack Overflow用户
发布于 2021-04-12 13:41:35
您可以为此使用regexp_like函数: where regexp_like(colName，'apple : 0-9')
https://stackoverflow.com/questions/67059223
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号