sql(Oracle)读取json_list数据和多层嵌套的json数据

sql(Oracle)读取json_list数据和多层嵌套的json数据

最近在开发一个管理系统的时候整理出一个多层嵌套的逻辑，并且在嵌套的最里层存在一对多的业务关系：

简单举例说明：每一层都有属于自己的属性，这样就简单构造出一个三层带json_list的json报文：

create or replace procedure pro_str_json(
o_code out int,
i_str in VARCHAR2
)
is

---------------------------------------------------------------------------

json_varchar2 varchar2(4000);
inJson json;
paramlist json_list;
paramJson json;
oneJson json;

--变量
--第一层
db_marketcode VARCHAR2(8);
db_marketname VARCHAR2(64);
db_address VARCHAR2(64);
db_tel VARCHAR2(11);
--第二层
db_name VARCHAR2(64);
db_fruitcode VARCHAR2(8);

begin
json_varchar2:='{
"marketcode": "1233336",
"marketname": "超市名字",
"address": "地点",
"tel": "12666666601",
"fruitlist": {
"name": "apple",
"fruitcode": "223344",
"applelist": [
{
"applename": "redfdwple ",
"applecode": "113330",
"price": "10"
},
{
"applename": "grefdwpple ",
"applecode": "1144411",
"price": "12"
},
{
"applename": "yellttwple ",
"applecode": "114522",
"price": "8"
}
]
}
}';
inJson := json(json_varchar2);
--获取第一层json值
db_marketcode:=json_ext.get_string(inJson,'marketcode');
db_marketname:=json_ext.get_string(inJson,'marketname');
db_address:=json_ext.get_string(inJson,'address');
db_tel:=json_ext.get_string(inJson,'tel');
--第二层
db_name:=json_ext.get_string(inJson,'fruitlist.name');
db_fruitcode:=json_ext.get_string(inJson,'fruitlist.fruitcode');
--接下来获取第三层，使用json_list来存放json列表
paramJson := json();
paramlist := json_list();
oneJson := json();
paramlist :=json_ext.get_json_list(inJson,'fruitlist.applelist');
--使用循环返回每个json部分的值
FOR i in 1..paramlist.count LOOP

oneJson := JSON(paramlist.get(i));

dbms_output.put_line(json_ext.get_string(oneJson,'applename'));
dbms_output.put_line(json_ext.get_string(oneJson,'applecode'));
dbms_output.put_line(json_ext.get_string(oneJson,'price'));

end loop;

exception
when others then
o_code := -1;
end pro_str_json;