Создать Oracle JSON из столбца типа данных CLOB

Ниже я установил таблицу с несколькими входными строками для тестирования; Затем я покажу один из способов решения вашей проблемы и результаты этого запроса. Я не пытался написать самый эффективный (самый быстрый) запрос; скорее, я надеюсь, это покажет вам, как это можно сделать. Тогда, если скорость является проблемой, вы можете поработать над этим. (В этом случае было бы лучше сначала пересмотреть входные данные, которые нарушают Первую нормальную форму.)

Я добавил пару строк ввода для тестирования, чтобы увидеть, как обрабатывается null. Вы можете решить, является ли это желаемой обработкой. (Возможно, что в ваших данных невозможно null - в этом случае вы должны были сказать об этом, задавая вопрос.)

Настройка тестовой таблицы:

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;

Запрос:

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
;

Вывод:

 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}]}