APEX_JSON.get_varchar2 in PL/SQL

Question

I am trying to extract the value from "response.AAPL.results.year_high.data" since there is two value. I have found many examples, but the value bracket in data is all individually identify by a title, mine is not. Does anybody know's how to access the information ?

in Oracle 12

thanks in advance.

     create or replace procedure test(p_where in varchar2, p_radius in number, p_room in number)
    is

    begin
      DECLARE
    l_param_list     VARCHAR2(512);
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_response_text  VARCHAR2(32767);
    l_members  WWV_FLOW_T_VARCHAR2;
    l_count     PLS_INTEGER;

    l_list json_list;
    obj json := json();
    l_json_values apex_json.t_values;
    arr json_list := json_list();

    l_paths apex_t_varchar2;

    BEGIN


     l_response_text := '{"response": {"AAPL": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": [["2016-09-30", 123.8200], ["2016-09-29", 125.0000]]}}}, "MSFT": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": ["2016-09-30", 58.7000]}}}}}';

     /*  DBMS_OUTPUT.put_line(l_response_text);*/
      apex_json.parse (l_response_text);
    /*  dbms_output.put_line (apex_json.get_varchar2(p_path => 'count')); */
    /*  dbms_output.put_line (apex_json.get_number (p_path   => 'response.MSFT.results.price.data',p0=>2,p_values =>l_json_values));*/

      DBMS_OUTPUT.put_line('----------------------------------------'); 
      DBMS_OUTPUT.put_line('Check elements (members) below a path');

      l_members := APEX_JSON.get_members(p_path=>'response.AAPL.results.year_high');
      DBMS_OUTPUT.put_line('Members Count     : ' || l_members.COUNT);

      FOR i IN 1 .. l_members.COUNT LOOP
        DBMS_OUTPUT.put_line('Member Item Idx   : ' || i); 
        DBMS_OUTPUT.put_line('Member Name       : ' || l_members(i)); 
      END LOOP;   




 /* This is were I would like to extract the value in the data member*/




      DBMS_OUTPUT.put_line('----------------------------------------'); 
      DBMS_OUTPUT.put_line('Employee Information (Loop through array)');

      l_count := APEX_JSON.get_count(p_path => 'response.AAPL.results.year_high.data');
      DBMS_OUTPUT.put_line('Employees Count   : ' || l_count);

      FOR i IN 1 .. l_count LOOP
        DBMS_OUTPUT.put_line('Employee Item Idx : ' || i); 

        DBMS_OUTPUT.put_line('Employee Number   : ' ||
          APEX_JSON.get_varchar2(p_path => 'response.AAPL.results.year_high.data[%d]', p0 => i)); 

        DBMS_OUTPUT.put_line('Employee Name     : ' ||
          APEX_JSON.get_varchar2(p_path => 'response.AAPL.results.year_high.data[%d]', p0 => i)); 
      END LOOP;                                  

    /*  dbms_output.put_line (apex_json.get_varchar2 ('response.MSFT.results.year_high.data[%d]', 1));
      dbms_output.put_line (apex_json.get_varchar2('response.MSFT.results.year_high.data[%d]', 2));
      dbms_output.put_line (apex_json.get_varchar2 ('response.AAPL.results.year_high.data[%d]',1));
      dbms_output.put_line (apex_json.get_varchar2('response.AAPL.results.year_high.data[%d]',2));
    */

    end;

    end test;

Answer 1

First of, it's very weird behavior to have an array of different data types. i.e.: [["2016-09-30", 123.8200], ["2016-09-29", 125.0000]]

Normally you would have an array of dates, array of numbers, array of text. Not mixed.

As you can see the data is an array of arrays. So you need to address that:

declare
   json   varchar2 (32767)
      := '{"response": {"AAPL": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": [["2016-09-30", 123.8200], ["2016-09-29", 125.0000]]}}}, "MSFT": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": ["2016-09-30", 58.7000]}}}}}';
begin
   apex_json.parse (json);

   dbms_output.put_line ('First value: ' || apex_json.get_varchar2 ('response.AAPL.results.year_high.data[1][1]'));
   dbms_output.put_line ('Second value: ' || apex_json.get_number ('response.AAPL.results.year_high.data[1][2]'));
end;

Will output:

First value: 2016-09-30
Second value: 123,82

EDIT:

To use a loop, this why it's bad to mix types in an array. Thankfully get_varchar2 will cast a number to text and not result in an error:

declare
   json   varchar2 (32767)
      := '{"response": {"AAPL": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": [["2016-09-30", 123.8200], ["2016-09-29", 125.0000]]}}}, "MSFT": {"meta": {"status": "ok"}, "results": {"year_high": {"meta": {"status": "ok"}, "data": ["2016-09-30", 58.7000]}}}}}';
begin
   apex_json.parse (json);

   for x in 1 .. nvl (apex_json.get_count ('response.AAPL.results.year_high.data'), -1) loop
      for y in 1 .. nvl (apex_json.get_count ('response.AAPL.results.year_high.data[%d]', x), -1) loop
         dbms_output.put_line ('First value: ' || apex_json.get_varchar2 ('response.AAPL.results.year_high.data[%d][%d]', x, y));
      end loop;
   end loop;
end;

Outputs:

First value: 2016-09-30
First value: 123.82
First value: 2016-09-29
First value: 125