CODEWITHSUNDEEP
oracle-apex
LNM
LNM

Reputation: 1

Extract Array value without attribute name - Oracle Apex_json

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;

Upvotes: 0

Views: 486

Answers (1)

Hamit Ünügür
Hamit Ünügür

Reputation: 21

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;

Upvotes: 1

Related Questions