Parsing JSON data in Oracle Apex with APEX_JSON.parse

Question

How to parse below JSON by using apex_json.parse APEX_JSON.get_varchar2(p_path => 'meta.columns'); It's giving me the numeric or value error. {"data": [["2021-01-01", 22, 22, 204, 0.09090909090909091, 22.545454545454547, 1.0, 6], ["2021-01-02", 25, 25, 240, 0.4, 21.16, 1.0, 17], ["2021-01-03", 22, 22, 195, 0.2727272727272727, 21.59090909090909, 1.0, 9], ["2021-01-04", 25, 25, 175, 0.16, 17.56, 1.0, 7], ["2021-01-05", 27, 27, 181, 0.18518518518518517, 16.925925925925927, 1.0, 6], ["2021-01-06", 21, 21, 137, 0.23809523809523808, 18.333333333333332, 1.0, 7], ["2021-01-07", 18, 18, 149, 0.2222222222222222, 19.0, 1.0, 11], ["2021-01-08", 27, 27, 206, 0.2222222222222222, 20.703703703703702, 1.0, 11], ["2021-01-09", 17, 17, 129, 0.23529411764705882, 17.647058823529413, 1.0, 5], ["2021-01-10", 21, 21, 173, 0.2857142857142857, 18.714285714285715, 1.0, 8]], "meta": {"columns": ["timestamp__to_date", "visitors", "sessions", "page_views", "goal_conversion_rate", "events_per_session", "returning_visitors_rate", "goal_conversions"], "count": 89}}

Answer 1

APEX_JSON.GET_VARCHAR2 returns a varchar2 member value. The json you posted has an array object with multiple member values for the meta.columns path. In that case loop through the array and use APEX_JSON.GET_VARCHAR2 for each array member value.

SET SERVEROUTPUT ON
DECLARE
  l_json_text VARCHAR2(32767);
  l_json_values    apex_json.t_values;
BEGIN
  l_json_text := '{
   "data":[
      [
         "2021-01-01",
         22,
         22,
         204,
         0.09090909090909091,
         22.545454545454547,
         1.0,
         6
      ],
      [
         "2021-01-10",
         21,
         21,
         173,
         0.2857142857142857,
         18.714285714285715,
         1.0,
         8
      ]
   ],
   "meta":{
      "columns":[
         "timestamp__to_date",
         "visitors",
         "sessions",
         "page_views",
         "goal_conversion_rate",
         "events_per_session",
         "returning_visitors_rate",
         "goal_conversions"
      ],
      "count":89
   }
}
';  


  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );

  DBMS_OUTPUT.put_line('----------------------------------------'); 
    IF apex_json.does_exist(p_path => 'meta.columns',p_values => l_json_values) THEN 
      dbms_output.put_line('meta.columns array elements: '||nvl(apex_json.get_count(p_path => 'meta.columns', p_values => l_json_values),0));
      FOR r IN 1 .. nvl(apex_json.get_count(p_path => 'meta.columns', p_values => l_json_values),0) loop
        dbms_output.put_line(apex_json.get_varchar2(p_path => 'meta.columns[%d]', p0 => r, p_values => l_json_values));
      END LOOP;
    END IF; 
  DBMS_OUTPUT.put_line('----------------------------------------'); 
END;
/

PL/SQL procedure successfully completed.

----------------------------------------
meta.columns array elements: 8
timestamp__to_date
visitors
sessions
page_views
goal_conversion_rate
events_per_session
returning_visitors_rate
goal_conversions
----------------------------------------