Extract Array value without attribute name - Oracle Apex_json

Question

DECLARE

lv_json_values      apex_json.t_values;
lv_items_count      NUMBER := 0;
lv_id     NUMBER := 0;

ip_json CLOB := '{ "items": [{ "tableName": "id_TRACKER", "count": 3, "columnNames": ["id"], "rows": [ ["9"], ["1"], ["2"]] }] }';

BEGIN 
    APEX_JSON.parse (p_source => ip_json, p_values => lv_json_values);
    lv_items_count := apex_json.get_number(p_path => 'items[1].count',
                                p_values => lv_json_values); 
                                
    dbms_output.put_line ('items Count : '||lv_items_count); 

    FOR lr_1 IN 1 .. lv_items_count
    LOOP
        lv_id := apex_json.get_number(p_path => 'items[1].rows[%d].',
            p0 => 1, p1 => lr_1, p_values => lv_json_values);
    
        dbms_output.put_line ('id  : '||lv_id); 
    END LOOP;
end;

Since values id values 9,1 and 2 are wrapped as array inside array, all attempts to get all 3 values have failed,

I can get only the 1st id; when I do loop it this way; however - can never get 2nd and 3rd value. Any help will be appreciated very much.

FOR lr_1 IN 1 .. lv_items_count
LOOP 
    lv_id := apex_json.get_number(p_path => 'items[1].rows[1][%d]',
        p0 => lr_1, p_values => lv_json_values);
        
dbms_output.put_line ('id  : '||lv_id);             
END LOOP;

Hamit Ünügür · Accepted Answer · 2022-04-01 14:13:43Z

1

Here is your answer;

DECLARE
lv_json_values      apex_json.t_values;
lv_items_count      NUMBER := 0;
lv_id     NUMBER := 0;
ip_json CLOB := '{ "items": [{ "tableName": "id_TRACKER", "count": 3, "columnNames": ["id"], "rows": [ ["9"], ["1"], ["2"]] }] }';
BEGIN 
    APEX_JSON.parse (p_source => ip_json, p_values => lv_json_values);
    lv_items_count := apex_json.get_count(p_path => 'items[1].rows',p_values => lv_json_values); 
    dbms_output.put_line ('items Count : '||lv_items_count); 
    FOR i IN 1 .. lv_items_count
    LOOP
        lv_id := apex_json.get_number(p_path => 'items[1].rows[%d][1]',p0 => i, p_values => lv_json_values);
        dbms_output.put_line ('id  : '||lv_id); 
    END LOOP;
end;

answered Apr 1, 2022 at 14:13

Hamit Ünügür

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LNM Over a year ago

I was doing some trial and error and ended up with same change as you suggested around the same time. Thanks once again.

Collectives™ on Stack Overflow

Extract Array value without attribute name - Oracle Apex_json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related