arrays - APEX_JSON.get_count 无法识别 JSON 路径

Question

我们在 Oracle 11g 上，目前没有升级的机会。此 JSON 来自外部来源，无法重新格式化。

这是 JSON 的问题吗？我能够使用简单的 JSON 运行 htis 代码。APEX_JSON.get_count 似乎只识别路径 hits.hits 我需要创建一个内部循环来解析 full_na

   declare
       l_cnt     PLS_INTEGER ;
       l_cnt_2   PLS_INTEGER ;  
       json   varchar2 (32767)
       := '{
      "took" : 45,
      "timed_out" : false,
      "_shards" : {
        "total" : 102,
        "successful" : 102,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : 1,
        "max_score" : 0.0,
        "hits" : [
          {
            "_index" : "pprepv5",
            "_type" : "_doc",
            "_id" : "pty990183115",
            "_score" : 0.0,
            "_routing" : "16",
            "_source" : {
              "pty_id" : 990183115,
              "asgmt_typ_cde" : null,
              "country_code" : null,
              "decd_ind" : "N",
              "pay_dir_ind" : "N",
              "paybl_ind" : null,
              "paybl_soc_cde" : null,
              "paybl_soc_pty_id" : null,
              "pty_typ_cde" : "NFMBR",
              "succr_act_fl" : null,
              "txfr_pty_id" : null,
              "del_fl" : "N",
              "ipi_bas_nr" : "I-001775403-7",
              "pty_rol_typ_cde" : "W",
              "document_type" : "entity",
              "entity_type" : "pty",
              "my_join_field" : "entity"
            },
            "inner_hits" : {
              "name" : {
                "hits" : {
                  "total" : 3,
                  "max_score" : 10.105857,
                  "hits" : [
                    {
                      "_index" : "pprepv5",
                      "_type" : "_doc",
                      "_id" : "name11627766990183115",
                      "_score" : 10.105857,
                      "_routing" : "16",
                      "_source" : {
                        "pty_na_id" : 1.1627766E7,
                        "del_fl" : "N",
                        "suf" : null,
                        "pty_id" : 990183115,
                        "na" : "LAMBERT",
                        "fst_na" : "MIRANDA",
                        "sal" : null,
                        "mid_init" : null,
                        "pty_na_typ_cde" : "PP",
                        "ipi_na_nr" : 4.00943E8,
                        "full_na" : "LAMBERT MIRANDA",
                        "suf_oth" : null,
                        "my_join_field" : {
                          "name" : "name",
                          "parent" : "pty990183115"
                        },
                        "document_type" : "name"
                      }
                    },
                    {
                      "_index" : "pprepv5",
                      "_type" : "_doc",
                      "_id" : "name11627765990183115",
                      "_score" : 8.721075,
                      "_routing" : "16",
                      "_source" : {
                        "pty_na_id" : 1.1627765E7,
                        "del_fl" : "N",
                        "suf" : null,
                        "pty_id" : 990183115,
                        "na" : "LAMBERT",
                        "fst_na" : "MIRANDA",
                        "sal" : null,
                        "mid_init" : "L",
                        "pty_na_typ_cde" : "PP",
                        "ipi_na_nr" : 4.00942985E8,
                        "full_na" : "LAMBERT MIRANDA L",
                        "suf_oth" : null,
                        "my_join_field" : {
                          "name" : "name",
                          "parent" : "pty990183115"
                        },
                        "document_type" : "name"
                      }
                    },
                    {
                      "_index" : "pprepv5",
                      "_type" : "_doc",
                      "_id" : "name11627764990183115",
                      "_score" : 8.721075,
                      "_routing" : "16",
                      "_source" : {
                        "pty_na_id" : 1.1627764E7,
                        "del_fl" : "N",
                        "suf" : null,
                        "pty_id" : 990183115,
                        "na" : "LAMBERT",
                        "fst_na" : "MIRANDA",
                        "sal" : null,
                        "mid_init" : "LEIGH",
                        "pty_na_typ_cde" : "PA",
                        "ipi_na_nr" : 4.00942887E8,
                        "full_na" : "LAMBERT MIRANDA LEIGH",
                        "suf_oth" : null,
                        "my_join_field" : {
                          "name" : "name",
                          "parent" : "pty990183115"
                        },
                        "document_type" : "name"
                      }
                    }
                  ]
                }
              }
            }
          }
        ]
      }
    }';

    begin
       apex_json.parse (json);
            l_cnt := APEX_JSON.get_count(p_path => 'hits.hits');       
          DBMS_OUTPUT.put_line('hits.hits Count   : ' || l_cnt);  

            l_cnt_2 := APEX_JSON.get_count(p_path => 'hits.hits.inner_hits.name.hits.hits');       
          DBMS_OUTPUT.put_line('inner before loop Count   : ' || l_cnt_2); 

       FOR i IN 1 .. l_cnt LOOP
              DBMS_OUTPUT.put_line('Counter : ' || i);
              DBMS_OUTPUT.put_line('pty_id   : ' ||
                APEX_JSON.get_varchar2(p_path => 'hits.hits[%d]._source.pty_id', p0 => i));

              l_cnt_2 := APEX_JSON.get_count(p_path => 'hits.hits[%d].inner_hits.name.hits.hits');            
              DBMS_OUTPUT.put_line('inner after loop Count   : ' || l_cnt_2);  

       end loop;
    end;

标签： arraysoracle11g