CODEWITHSUNDEEP
jsonoracle-databaseoracle11g
user10531062
user10531062

Reputation: 197

Extract text from a CLOB datatype field in Oracle table

Can some one please advise how to extract text from the field below? It is a JSON file which is stored as a CLOB datatype field in an Oracle table.

The field name is Rules and its value is as below:

{
   "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)"
   }
}

I want to extract the two IP address from above in two different fields as shown below:

enter image description here

Upvotes: 0

Views: 355

Answers (1)

user5683823
user5683823

Reputation:

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

Upvotes: 2

Related Questions