Oracle 解析无密钥的JSON数据

我试图从api获取JSON数据。

JSON数据看起来像这样:

[
  1354830081,
  99005338,
  498125261,
  99003581,
  99003214,
  99008228,
  1900696668,
  99003006,
  99009268,
  1911932230,
  99007362,
  99004514,
  1727758877,
  99007044,
  131511956,
  1411711376,
  99002367,
  99002938,
  99004425,
  99008223,
  99009082,
  99003838,
  99007149,
  99004116,
  448511760,
  386292982,
  741557221,
  99003212,
  99007498,
  1988009451,
  1042504553,
  1220922756,
  99003995,
  117383987,
  99006828,
  154104258,
  99007203,
  99004901,
  937872513,
  150097440,
  99001954,
  99003714,
  1496500070,
  99008259,
  99007221,
  99003144,
  99006961,
  99008879,
  99008301,
  99001099,
  99003549,
  99008802,
  99005874,
  499005583,
  99007871,
  99002003,
  99005065,
  99002775,
  99007391,
  673381830,
  99008469,
  434243723,
  99002107,
  99008809,
  982284363,
  99001317,
  99001648,
  99006820,
  99007574,
  99008458,
  99006069,
  99007716,
  99008493,
  99005518,
  99009201,
  99007969,
  99004344,
  99008788,
  99007252,
  288377808,
  679584932,
  99005697,
  99004136,
  99008556,
  173714703,
  99005100,
  99005443,
  99001657,
  99006985,
  99007916,
  1681527727,
  99008976,
  99008826,
  99005688,
  707482380,
  99007289,
  99006941,
  99001134,
  99009163]

所以我没有得到密钥，而只有id作为回报。

我已经尝试了以下几点:

      apex_json.parse(p_values => l_values, p_source => l_clob);  
      
      l_num := APEX_JSON.get_count(p_path => '.',p_values => l_values);
      DBMS_OUTPUT.put_line('Lines: '||l_num);
    FOR i IN 1 .. l_num
    LOOP
    v_data := apex_json.get_number('.$',i);
        --INSERT INTO TBL_ALL_ALLIANCES VALUES (v_data);         
    DBMS_OUTPUT.put_line('- Data: '||v_data);
    END LOOP;

   

但即使只有。在apex_json.get_number中，我无法获得返回的值，但是我确实从apex_json.get_number中获得了正确的行数，但是我似乎没有获得正确的值。有谁知道如何从没有键的行中获取值？

你只是引用你想要的数组的元素，例如 [3]:

declare
  l_values varchar2(1000) := '[
  1354830081,
  99005338,
  498125261,
  99003581,
  99003214,
  99008228,
  1900696668,
  99003006,
  99009268,
  1911932230]';
  v_data int;
  l_num int;
begin

    apex_json.parse(l_values);  
      
    l_num := APEX_JSON.get_count(p_path => '.');
    DBMS_OUTPUT.put_line('Lines: '||l_num);
    FOR i IN 1 .. l_num
    LOOP
      v_data := apex_json.get_number('[' || i || ']',i);     
      DBMS_OUTPUT.put_line('- Data: '||v_data);
    END LOOP;
end;
/

Lines: 10
- Data: 1354830081
- Data: 99005338
- Data: 498125261
- Data: 99003581
- Data: 99003214
- Data: 99008228
- Data: 1900696668
- Data: 99003006
- Data: 99009268
- Data: 1911932230

从12.1.0.2您也可以在SQL中使用JSON_table执行此操作:

with rws as (
  select '[
  1354830081,
  99005338,
  498125261,
  99003581,
  99003214,
  99008228,
  1900696668,
  99003006,
  99009268,
  1911932230]' j 
  from dual
)
  select arr.*
  from   rws, json_table (
    j, '$'
    columns ( 
      nested path '$[*]' 
      columns (
        c1 int path '$'
      )
    )
  ) arr;

C1           
   1354830081 
     99005338 
    498125261 
     99003581 
     99003214 
     99008228 
   1900696668 
     99003006 
     99009268 
   1911932230