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

OracleOracle 解析复杂类型解析复杂类型 jsonjson 的实例分享的实例分享工作中常遇到用 oracle 解析复杂类型的 json（多层对象并且包含数组等）情况，本人在开发中，采用将复杂 json 逐层解析到表，随后再提取方法。例：解析如下的 json 数据，某平台国内机票订购单据? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34“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“: “陳智偉“,35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78“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“,79 80 81 82 83“timeStamp“: “20180721111947904“,“msgType“: “TBOrderInfo“638 棋牌 http:/www.rodlg.com构建 json 解析结果表：? 1 2 3 4 5 6 7 8 9 10 11 12 13create 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 表序列：? 1 2 3 4 5create sequence JSON_SEQ minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1;创建解析 json 解析过程：? 1 2 3 4 5 6 7 8 9 10 11 12 13create 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) isv_member varchar2(100);v apex_json.t_value;v_path varchar2(1000) := path;14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57-v_cnt integer; 易火棋牌 http:/www.482223.com-v_str varchar2(32700);v_ret varchar2(1000);- v_seq integer;v_current_id integer;v_item varchar2(200);beginselect 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);casewhen v.kind is null thennull;v_ret := apex_json.get_varchar2(p_path => v_path, p_values => j);when v.kind in (1, 2, 3) then58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101null;v_ret := apex_json.get_varchar2(p_path => v_path, p_values => j);when v.kind = 4 thenv_ret := to_char(v.number_value);when v.kind = 5 thenv_ret := v.varchar2_value;when v.kind in (6) thennull;-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) loopv_member := v.object_members(i); -apex_json.get_members(p_path => path, p_values => j) (i);- dbms_output.put_line(v_member);if path != '.' thenv_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) then102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145-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 != '.' thenv_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;elsenull;end case;-get node item namev_item := substr(v_path, instr(v_path, '.', -1) + 1);-store into tableinsert into tb_json_data_detail(id, json_id, path, kind, val, parent_id, lvl, item, seq_no)146 147 148 149 150 151 152 153 154values(v_current_id,json_id,v_path,v.kind,v_ret,parent_id,lvl,v_item,seq_no);commit;end pr_json_nest_2;至此，