英文:

Parsing JSON data in Oracle Apex with APEX_JSON.parse

问题

You can parse the JSON using apex_json.parse in Apex, and then retrieve the value for the 'meta.columns' path using APEX_JSON.get_varchar2(p_path => 'meta.columns').

Here's the translated code part:
你可以使用Apex中的apex_json.parse来解析JSON，然后使用APEX_JSON.get_varchar2(p_path => 'meta.columns')来获取'meta.columns'路径的值。

英文:

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}}

答案1

得分: 0

APEX_JSON.GET_VARCHAR2 返回一个 varchar2 成员值。您发布的 JSON 具有包含多个成员值的数组对象，位于 meta.columns 路径下。在这种情况下，循环遍历数组并对每个数组成员值使用 APEX_JSON.GET_VARCHAR2。

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 数组元素: '||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 过程成功完成。

----------------------------------------
meta.columns 数组元素: 8
timestamp__to_date
visitors
sessions
page_views
goal_conversion_rate
events_per_session
returning_visitors_rate
goal_conversions
----------------------------------------```

<details>
<summary>英文:</summary>

```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

</details>