Chris Poi Chris Poi - 2 months ago 21
JSON Question

APEX_JSON.get_varchar2 in PL/SQL

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

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
Comments