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

请问有人可以建议如何从下面的字段中提取文本吗?它是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地址,如下所示,

enter image description here

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

评论
  • Donne
    Donne 回复

    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