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

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

简单举例说明：一家水果店出售多种水果（为了简化业务结构，就单纯一苹果为主线），苹果又分为多种类型（比如红苹果、青苹果、黄苹果），所以得到三层结构，每一层都有属于自己的属性，这样就简单构造出一个三层带json_list的json报文：

{
    "marketcode": "123456",
    "marketname": "好吃的水果店",
    "address": "一个好地方",
    "tel": "12345678901",
    "fruitlist": {
        "name": "apple",
        "fruitcode": "223344",
        "applelist": [
            {
                "applename": "redapple ",
                "applecode": "111000",
                "price": "10"
            },
            {
                "applename": "greenapple ",
                "applecode": "111111",
                "price": "12"
            },
            {
                "applename": "yellowapple ",
                "applecode": "111222",
                "price": "8"
            }
        ]
    }
}

接下来就是详细的解析代码：

declare
  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": "123456",
    "marketname": "好吃的水果店",
    "address": "一个好地方",
    "tel": "12345678901",
    "fruitlist": {
        "name": "apple",
        "fruitcode": "223344",
        "applelist": [
            {
                "applename": "redapple ",
                "applecode": "111000",
                "price": "10"
            },
            {
                "applename": "greenapple ",
                "applecode": "111111",
                "price": "12"
            },
            {
                "applename": "yellowapple ",
                "applecode": "111222",
                "price": "8"
            }
        ]
    }
}';
  inJson := json(json_varchar2);
--获取第一层json值
  db_marketcode=json_get_string(inJson,'marketcode');
  db_marketname=json_get_string(inJson,'marketname');
  db_address=json_get_string(inJson,'address');
  db_tel=json_get_string(inJson,'tel');
--第二层
  db_name:=json_get_string_ext(inJson,'fruitlist.name');
  db_fruitcode:=json_get_string_ext(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_get_string(oneJson,'applename'));
    dbms_output.put_line(json_get_string(oneJson,'applecode'));
    dbms_output.put_line(json_get_string(oneJson,'price'));

    end loop;
end;

总结：以上解析过程，第一第二层因为直接获取值并赋值给数据库变量，在存储过程中可以多次使用，但是对于第三层的数据，如果需要将数据插入到表中就需要将插入语句放在循环中处理，因为循环的关系，第三层的数据在当前过程中不适合作为变量被其他地方使用；

以上内容由作者本人亲测并整理出来分享给大家，如果中间存在错误或者误导部分，望读者帮忙指正，如果有更好的处理同类型问题的方法，欢迎共同交流！