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