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%';