从Oracle表中的CLOB数据类型字段中提取文本

由偏执旳温柔发布于 2020-05-24 09:47:04

请问有人可以建议如何从下面的字段中提取文本吗？它是Json文件，存储为Oracle表中的CLOB数据类型字段。

字段名称是“规则”，其值如下所示，

{“ condition” [{“ name”：“”，“ property”：“ ipaddress”，“ type”：“ range”，“ operator”：“ range”，“ start”：“ 2.117.11.1”，“ end” ：“ 2.117.11.254”}“，”运算符“：{”属性“：”或“}，”结果“：{”名称“：” BRSBRS“，”原因“：”网站创建于2018年4月20日星期五：45：46 GMT + 0100（格林尼治标准时间夏令时）“}}

我想从上方在两个不同的字段中提取两个IP地址，如下所示，

感谢您抽出宝贵的时间对此进行调查。

Donne

2020-05-24 09:47:04

Assuming that you had a typo in your string, and it is, in fact, a valid JSON document (meaning: you are missing a colon after "condition", you can use the json_table() function, available since Oracle 12.1.

create table tbl (id number primary key, rules clob);
insert into tbl (id, rules) 
  select 1001,  '{"condition":[{"name":"","property":"ipaddress","type":"range","operator":"range","start":"2.117.11.1","end":"2.117.11.254"}],"operator":{"property":"or"},"outcome": {"name":"BRSBRS","reason":"Site was created on Fri Apr 20 2018 09:45:46 GMT+0100 (GMT Daylighttime)"}}'
  from   dual;

select id, start_ip_address, end_ip_address
from   tbl,
       json_table(rules format json, '$.condition[*]'
           columns start_ip_address varchar2(19) path '$.start',
                   end_ip_address   varchar2(19) path '$.end'
      )
;

  ID  START_IP_ADDRESS  END_IP_ADDRESS
----  ----------------  ----------------
1001  2.117.11.1        2.117.11.254

偏执旳温柔

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3206
注册排名
2232