时间戳

首页 > 解决方案 > 从 CLOB 数据类型列生成 Oracle JSON

json - 从 CLOB 数据类型列生成 Oracle JSON

问题描述

要求是从 clob 数据类型列生成 JSON。环境版本 Oracle 12.2

我有一个带有字段 id（数字数据类型）和详细信息（clob 类型）的表，如下所示

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

例如输出：

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

请帮助使用 oracle SQL 查询以生成输出。

标签： jsonoracleoracle12cclob

解决方案

下面我设置了一个表，其中包含一些输入行进行测试；然后我展示了一种解决问题的方法，以及该查询的输出。我没有尝试编写最有效（最快）的查询；相反，我希望这将向您展示如何做到这一点。然后，如果速度是一个问题，你可以解决这个问题。（在这种情况下，最好先重新考虑输入，这会破坏第一范式。）

我添加了几个输入行进行测试，看看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}]}

推荐阅读