CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle-apex
Paweł Żurkiewicz
Paweł Żurkiewicz

Reputation: 257

Parsing JSON in Oracle APEX 19.2

I'm trying to parse a json, and get data from it and put it in apex collection.

DECLARE
     s            varchar2(32767) := APEX_APPLICATION.g_x01;
     j            apex_json.t_values; 
     v            apex_json.t_value; 
     n_count      number;
     temp         varchar2(1000);

Begin

    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
        p_collection_name => 'Settings'
        );

    apex_json.parse(p_values => j, p_source => s);

    n_count := apex_json.get_count(p_path => 'Attribute', p_values => j);   

            for i in 1 .. n_count
            loop
                        v := apex_json.get_value(p_path => 'TEST[%d]', p0 => i, p_values => j);
                        temp := v.varchar2_value;
                        APEX_COLLECTION.ADD_MEMBER(
                        p_collection_name => 'Settings',
                            p_c001 => 'TEST',
                            p_c002 => temp
                        );
            end loop;  
end;

and its working good, when the Test attribute is an array. But when it is not there is a problem. I can easily handle the single value with 

apex_json.parse(s);
         APEX_COLLECTION.ADD_MEMBER(
                p_collection_name => 'Settings',
                p_c001 => 'Test',
                p_c002 => apex_json.get_varchar2(p_path => 'TEST')
                );

But i don't know how to check at the start what type is TEST (array, or varchar2)

Upvotes: 0

Views: 992

Answers (1)

Paweł Żurkiewicz
Paweł Żurkiewicz

Reputation: 257

I solved that problem with apex_json.does_exist function with parameter p_values, p_path, p0. First we must check if there is a array with name 'TEST' with 1 attribute. Then just make a if-then-else statement. Solution below

DECLARE
     s            varchar2(32767) := APEX_APPLICATION.g_x01;
     j            apex_json.t_values; 
     v            apex_json.t_value; 
     n_count      number;
     temp         varchar2(1000);

Begin

    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
        p_collection_name => 'Settings'
        );

    apex_json.parse(p_values => j, p_source => s);

    if apex_json.does_exist(p_values => j, p_path => 'TEST[%d]', p0 => 1) then
        n_count := apex_json.get_count(p_path => 'TEST', p_values => j);    
        for i in 1 .. n_count
            loop
                        v := apex_json.get_value(p_path => 'TEST[%d]'    , p0 => i, p_values => j);
                        temp := v.varchar2_value;
                        APEX_COLLECTION.ADD_MEMBER(
                        p_collection_name => 'Settings',
                            p_c001 => 'TEST',
                            p_c002 => temp
                        );
            end loop;         
    else 
        apex_json.parse(s);
             APEX_COLLECTION.ADD_MEMBER(
                    p_collection_name => 'Settings',
                    p_c001 => 'TEST',
                    p_c002 => apex_json.get_varchar2(p_path => 'TEST')
                    );         
    end if;

end;

Upvotes: 1

Related Questions