Generate Oracle JSON from CLOB datatype column

Question

Requirement is to generate JSON from clob data type column. environment version Oracle 12.2

I have a table with fields id (number data type) and details (clob type) like below

ID   - details 

100  - 134332:10.0, 1481422:1.976, 1483734:1.688, 2835036:1.371

101  - 134331:0.742, 319892:0.734, 1558987:0.7, 2132090:0.697

eg output:

{
   "pId":100,
   "cid":[
      {
         "cId":134332,
         "wt":"10.0"
      },
      {
         "cId":1481422,
         "wt":"1.976"
      },
      {
         "cId":1483734,
         "wt":"1.688"
      },
      {
         "cId":2835036,
         "wt":"1.371"
      }
   ]
}

please help with oracle SQL query to generate output.

Answer 1

Below I set up a table with a few input rows for testing; then I show one way you can solve your problem, and the output from that query. I didn't try to write the most efficient (fastest) query; rather, I hope this will show you how this can be done. Then if speed is a problem you can work on that. (In that case, it would be best to reconsider the inputs first, which break First Normal Form.)

I added a couple of input rows for testing, to see how null is handled. You can decide if that is the desired handling. (It is possible that no null are possible in your data - in which case you should have said so when you asked the question.)

Setting up the test table:

create table input_tbl (id number primary key, details clob);
insert into input_tbl (id, details) values
  (100, to_clob('134332:10.0, 1481422:1.976, 1483734:1.688, 2835036:1.371'));
insert into input_tbl (id, details) values
  (101, '134331:0.742, 319892:0.734, 1558987:0.7, 2132090:0.697');
insert into input_tbl (id, details) values
  (102, null);
insert into input_tbl (id, details) values
  (103, '2332042:  ');
commit;

Query:

with
  tokenized (pid, ord, cid, wt) as (
    select i.id, q.ord, q.cid, q.wt
    from   input_tbl i cross apply
           (
             select level as ord, 
                    regexp_substr(details, '(, |^)([^:]+):', 1, level, null, 2) 
                      as cid,
                    regexp_substr(details, ':([^,]*)', 1, level, null, 1) as wt
             from   dual
             connect by level <= regexp_count(details, ':')
           ) q
  )
, arrayed (pid, json_arr) as (
    select pid, json_arrayagg(json_object(key 'cId' value to_number(trim(cid)),
                                          key 'wt'  value to_number(trim(wt)))
                             )
    from   tokenized
    group  by pid
  )
select pid, json_object(key 'pId' value pid, key 'cid' value json_arr) as json
from   arrayed
;

Output:

 PID JSON                                                                                                                         
---- -----------------------------------------------------------------------------------------------------------------------------
 100 {"pId":100,"cid":[{"cId":134332,"wt":10},{"cId":2835036,"wt":1.371},{"cId":1483734,"wt":1.688},{"cId":1481422,"wt":1.976}]}  
 101 {"pId":101,"cid":[{"cId":134331,"wt":0.742},{"cId":2132090,"wt":0.697},{"cId":1558987,"wt":0.7},{"cId":319892,"wt":0.734}]}  
 102 {"pId":102,"cid":[{"cId":null,"wt":null}]}                                                                                   
 103 {"pId":103,"cid":[{"cId":2332042,"wt":null}]}