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

18-07-27 来源：[db:作者]

收藏我要投稿

工作中常遇到用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%';

点击复制链接与好友分享!回本站首页