欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle解析复杂类型json的实例分享

程序员文章站 2022-07-01 19:11:57
工作中常遇到用oracle解析复杂类型的json(多层对象并且包含数组等)情况,本人在开发中,采用将复杂json逐层解析到表,随后再提取方法。 例:解析如下的json数据,某平台国内机票订购单据...

工作中常遇到用oracle解析复杂类型的json(多层对象并且包含数组等)情况,本人在开发中,采用将复杂json逐层解析到表,随后再提取方法。

例:解析如下的json数据,某平台国内机票订购单据

{

 "data": {

  "airportfee": 50.000000,

  "companyid": "c117507",

  "companyname": "it测试专用公司",

  "flightinfo": [{

   "airlinename": "南方航空",

   "arrivaldate": "2018-09-05",

   "cabin": "2",

   "clazz": "j",

   "departuredate": "2018-09-04",

   "destinationcityname": "上海(浦东)",

   "flightno": "cz3586",

   "origincityname": "广州"

  }],

  "issuteway": 0,

  "opname": "陳智偉",

  "orderno": "tb1800839048",

  "ordersource": 0,

  "orderstatus": "已处理",

  "ordertype": 1,

  "passenger": [{

   "passengerairportfee": 50.0,

   "passengercode": "p288725",

   "passengername": "陈智伟",

   "passengersaleprice": 3110.0,

   "passengersaleserviceprice": 0.0,

   "passengersaletaxtwo": 10.0,

   "passengertype": "成人",

   "ticketno": "784-2977101969"

  }],

  "pricetotal": 3170.000000,

  "purchasechannelstype": 0,

  "saleprice": 3110.000000,

  "saleserviceprice": 0.000000,

  "saletaxtwo": 10.000000,

  "starttime": "2018-07-21 10:43"

 },

 "password": "95aa19fb424fe74275f8608b90afbea344421346",

 "timestamp": "20180721111947904",

 "msgtype": "tborderinfo"

}

构建json解析结果表:

create table tb_json_data_detail
(
  id          integer not null,
  json_id     integer,
  path        varchar2(200),
  kind        varchar2(5),
  val         varchar2(2000),
  parent_id   integer,
  lvl         integer,
  create_time date default sysdate,
  item        varchar2(200),
  seq_no      integer
);

构建tb_json_data_detail表序列:

create sequence json_seq
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1;

创建解析json解析过程:


create or replace procedure pr_json_nest_2(json_id  integer,
                     j     apex_json.t_values,
                     parent_id integer,
                     path   varchar2 default '.',
                     lvl    integer default 1,
                     seq_no  integer default 1) is

 v_member varchar2(100);

 v apex_json.t_value;

 v_path varchar2(1000) := path;

 --v_cnt integer;

 --v_str varchar2(32700);
 v_ret varchar2(1000);
 -- v_seq integer;

 v_current_id integer;

 v_item varchar2(200);

begin

 select json_seq.nextval into v_current_id from dual;

 /*subtype t_kind is binary_integer range 1 .. 7;
 c_null   constant t_kind := 1;
 c_true   constant t_kind := 2;
 c_false  constant t_kind := 3;
 c_number  constant t_kind := 4;
 c_varchar2 constant t_kind := 5;
 c_object  constant t_kind := 6;
 c_array  constant t_kind := 7;

 * c_number: number_value contains the number value

 * c_varchar2: varchar2_value contains the varchar2 value

 * c_object: object_members contains the names of the object's members

 * c_array: number_value contains the array length
 */

 v := apex_json.get_value(p_path => path, p_values => j);

 case
  when v.kind is null then
 
   null;
 
   v_ret := apex_json.get_varchar2(p_path => v_path, p_values => j);
 
  when v.kind in (1, 2, 3) then
 
   null;
 
   v_ret := apex_json.get_varchar2(p_path => v_path, p_values => j);
 
  when v.kind = 4 then
 
   v_ret := to_char(v.number_value);
 
  when v.kind = 5 then
 
   v_ret := v.varchar2_value;
 
  when v.kind in (6) then
 
   null;
   --get node name
   --v_item := substr(v_path, instr(v_path, '.', -1) + 1);
   --dbms_output.put_line(v.object_members(1));
 
   for i in 1 .. apex_json.get_count(p_path => path, p_values => j) loop
  
    v_member := v.object_members(i); --apex_json.get_members(p_path => path, p_values => j) (i);
  
    -- dbms_output.put_line(v_member);
  
    if path != '.' then
   
     v_member := path || '.' || v_member;
   
    end if;
  
    pr_json_nest_2(json_id  => json_id,
           j     => j,
           parent_id => v_current_id,
           path   => v_member,
           lvl    => lvl + 1,
           seq_no  => i);
  
   end loop;
 
  when v.kind in (7) then
 
   --dbms_output.put_line(v.number_value);
 
   v_ret := to_char(v.number_value);
 
   null;
 
   --dbms_output.put_line(v.object_members(1));
 
   for i in 1 .. v.number_value /*apex_json.get_count(p_path => path, p_values => j)*/
   loop
  
    -- v_member := v.object_members(i); --apex_json.get_members(p_path => path, p_values => j) (i);
  
    -- dbms_output.put_line(v_member);
  
    if path != '.' then
   
     v_member := v_path || '[' || i || ']';
   
    end if;
  
    pr_json_nest_2(json_id  => json_id,
           j     => j,
           parent_id => v_current_id,
           path   => v_member,
           lvl    => lvl + 1,
           seq_no  => i);
  
   end loop;
 
  else
 
   null;
 end case;

 --get node item name

 v_item := substr(v_path, instr(v_path, '.', -1) + 1);

 --store into table
 insert into tb_json_data_detail
  (id, json_id, path, kind, val, parent_id, lvl, item, seq_no)
 values
  (v_current_id,
  json_id,
  v_path,
  v.kind,
  v_ret,
  parent_id,
  lvl,
  v_item,
  seq_no);

 commit;

end pr_json_nest_2;

至此,json函数解析过程及json解析结果表已经构建完成,此时只需调用解析过程:

declare

  j apex_json.t_values;
  p_json clob;
  p_id  number;
  
begin
 p_json:='{"data":{"airportfee":50.000000,"companyid":"c117507","companyname":"it测试专用公司","flightinfo":[{"airlinename":"南方航空","arrivaldate":"2018-09-05","cabin":"2","clazz":"j","departuredate":"2018-09-04","destinationcityname":"上海(浦东)","flightno":"cz3586","origincityname":"广州"}],"issuteway":0,"opname":"陳智偉","orderno":"tb1800839048","ordersource":0,"orderstatus":"已处理","ordertype":1,"passenger":[{"passengerairportfee":50.0,"passengercode":"p288725","passengername":"陈智伟","passengersaleprice":3110.0,"passengersaleserviceprice":0.0,"passengersaletaxtwo":10.0,"passengertype":"成人","ticketno":"784-2977101969"}],"pricetotal":3170.000000,"purchasechannelstype":0,"saleprice":3110.000000,"saleserviceprice":0.000000,"saletaxtwo":10.000000,"starttime":"2018-07-21 10:43"},"password":"95aa19fb424fe74275f8608b90afbea344421346","timestamp":"20180721111947904","msgtype":"tborderinfo"}' ;
 p_id  :=1;
    apex_json.parse(j, p_json);

    --调用递归

    pr_json_nest_2(json_id   => p_id,
                   j         => j,
                   parent_id => null,
                   path      => '.',
                   lvl       => 1);
 

end;

提取数据,验证解析结果:p_id为上诉传参id

①订票主信息 

select *
    from (select /*a.parent_id,*/
       a.val, a.item
        from tb_json_data_detail a
       where json_id = 1 --p_id
        and lvl = 3)
   pivot(max(val)
    for item in('purchasechannelstype',
          'issuteway',
          'orderno',
          'ordertype',
          'orderstatus',
          -- 'flightinfo',
          'companyid',
          'companyname',
          'opname',
          -- 'passenger',
          'saleserviceprice',
          'airportfee',
          'saleprice',
          'saletaxtwo',
          'pricetotal',
          'ordersource',
          'starttime'));


 ---②航班信息
   selectairlinename,
      arrivaldate,
      cabin,
      clazz,
      departuredate,
      destinationcityname,
      flightno,
      origincityname
    from (select a.parent_id,
          a.val,
          a.item,
          (select item
            from tb_json_data_detail b
           where b.id = a.parent_id) parent_item
        from tb_json_data_detail a
       where json_id = 1 --p_id
        and lvl = 5)
   pivot(max(val)
    for item in('airlinename' as airlinename,
          'arrivaldate' as arrivaldate,
          'cabin' as cabin,
          'clazz' as clazz,
          'departuredate' as departuredate,
          'destinationcityname' as destinationcityname,
          'flightno' as flightno,
          'origincityname' as origincityname))
   where parent_item like'flightinfo%';

--③乘客信息
   selectpassengercode,
      passengertype,
      passengersaleserviceprice,
      passengerairportfee,
      passengersaleprice,
      passengersaletaxtwo,
      passengername,
      ticketno
    from (select a.parent_id,
          a.val,
          a.item,
          (select item
            from tb_json_data_detail b
           where b.id = a.parent_id) parent_item
        from tb_json_data_detail a
       where json_id = 1 --p_id
        and lvl = 5)
   pivot(max(val)
    for item in('passengerairportfee' as passengerairportfee,
          'passengercode' as passengercode,
          'passengername' as passengername,
          'passengersaleprice' as passengersaleprice,
          'passengersaleserviceprice' as
          passengersaleserviceprice,
          'passengersaletaxtwo' as passengersaletaxtwo,
          'passengertype' as passengertype,
          'ticketno' as ticketno))
   where parent_item like'passenger%';