How to use PL/JSON to parse array data

Question

I am just starting to use PL/JSON and I am able to parse non-array data, such as first name, for example:

json_ext.get_string (json (l_list.get (i)), 'firstName');

However I can't figure out how to parse array data such as addresses, for example:

street1 := json_ext.get_string (json (l_list.get (i)), 'addresses.street1');

does not work.

How would I correctly parse array data, using:

street1 := json_ext.get_string (json (l_list.get (i)), 'addresses.street1');

using as an example?

Here is an example of the JSON:

[
  {

"firstName": "Edward",   
"middleName": "Wolfgang",
"lastName": "Munster",    
"addresses": [
  {       
    "city": "",
    "addressType": "home",
    "state": "CA",
    "street1": "1313 Mockingbird Lane",
    "street2": ""
  },
  {        
    "city": "",
    "addressType": "business",
    "state": "CA",
    "street1": "123 Morgan Rd.",
    "street2": ""
  }
],  
}

Here is the an example of the code I am using:

BEGIN
   SELECT json          
   INTO l_json
   FROM json_table
   WHERE id = 1;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            l_json := NULL;
    END;


l_list := json_list (l_json);

FOR i IN 1 .. l_list.COUNT
LOOP
--These parse correctly      
  firstname := json_ext.get_string (json (l_list.get (i)), 'firstName');        
  middlename := json_ext.get_string (json (l_list.get (i)), 'middleName');
  lastname := json_ext.get_string (json (l_list.get (i)), 'lastName');      

--These do not parse at all  
  addresstype :=  json_ext.get_string (json (l_list.get (i)), 'addresses.addressType');     
  street1 := json_ext.get_string (json (l_list.get (i)), 'addresses.street1');
  street2 := json_ext.get_string (json (l_list.get (i)), 'addresses.street2');
  city := json_ext.get_string (json (l_list.get (i)), 'addresses.city');
  state := json_ext.get_string (json (l_list.get (i)), 'addresses.state');

You're already parsing an outer array. What is your difficulty in parsing an inner array? It's the same action. — James Sumners, Apr 06 '16 at 12:34
@JamesSumners No data is returned for the inner array. It looks like json_ext.get_string (json (l_list.get (i)) does not work for a nested, inner array. — user6146386, Apr 06 '16 at 12:59
@JamesSumners Based on my previous answer, do you have any insight into what I am doing incorrectly? — user6146386, Apr 06 '16 at 13:12

score 2 · Answer 1 · answered Nov 07 '17 at 11:55

The example above didn't work for me. I managed to get the following working.

declare
     json_data2 json;
  json_data json := json('{   "foo": "bar",   "list": [     {"key": "value1"},     {"key": "value2"}   ] }');
  list_value json_list;
begin
  list_value := pljson_ext.get_json_list(json_data, 'list');

  dbms_output.put_line('Count = '||list_value.count);

  for i in 1 .. list_value.count
  loop
     -- json_data2 := json(list_value.get(i));
json(list_value.get(i)).get('key').print;
     -- dbms_output.put_line('key = ' || json_data2.get('key')); -- "key = value"
     -- json_data2.get('key').print;
  end loop;
end;
/

score 0 · Answer 2 · answered Apr 06 '16 at 17:28

It's quite simple. Just retrieve the list and iterate it:

-- {
--   "foo": "bar",
--   "list": [
--     {"key": "value"},
--     {"key": "value"}
--   ]
-- }

declare
  json_data json := '<the above JSON>';
  list_value json_list;
begin
  list_value := json_data.get('list');

  for i in 1 .. list_value.count
  loop
    dbms_output.put_line('key = ' || list_value[i].get('key')); -- "key = value"
  end;
end;
/

Marco Guerrero · Answer 3 · 2019-06-18T15:36:59.857

check this example:

DECLARE
  OBJ JSON := JSON( '{"pjDATOS_CITAS":[{"SDACLAVE":"32709","PENDIENTES_CITAR":"3","CANTIDAD":"1"},{"SDACLAVE":"32708","PENDIENTES_CITAR":"3","CANTIDAD":"0"}]}' );
  LIST_VALUE JSON_LIST;  
BEGIN
    LIST_VALUE := JSON_LIST(OBJ.GET('pjDATOS_CITAS'));
    FOR I IN 1 .. LIST_VALUE.COUNT LOOP                
        DBMS_OUTPUT.PUT_LINE( 'SDACLAVE->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'SDACLAVE') );
        DBMS_OUTPUT.PUT_LINE( 'PENDIENTES_CITAR->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'PENDIENTES_CITAR') );
        DBMS_OUTPUT.PUT_LINE( 'CANTIDAD->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'CANTIDAD') );
        DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------' );          
    END LOOP;
END;

and you need fix your JSON FILE... check this example with your JSON:

DECLARE
  OBJ JSON := JSON( '{"info_any": {"middleName": "Wolfgang","lastName": "Munster","addresses": [{"city": "","state": "CA","addressType": "home","street2": "","street1": "1313 Mockingbird Lane"},{"city": "","state": "CA","addressType": "business","street2": "","street1": "123 Morgan Rd."}],"firstName": "Edward"}}' );
  LIST_VALUE JSON_LIST;  
BEGIN

    OBJ := JSON ( OBJ.GET('info_any') );

    DBMS_OUTPUT.PUT_LINE( 'middleName->'||JSON_EXT.GET_STRING( OBJ , 'middleName') );
    DBMS_OUTPUT.PUT_LINE( 'firstName->' ||JSON_EXT.GET_STRING( OBJ , 'firstName') );
    DBMS_OUTPUT.PUT_LINE( 'lastName->'  ||JSON_EXT.GET_STRING( OBJ , 'lastName') );        
    DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------' );

    LIST_VALUE := JSON_LIST(OBJ.GET('addresses'));

    FOR i IN 1 .. LIST_VALUE.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE( 'city->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'city') );
        DBMS_OUTPUT.PUT_LINE( 'state->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'state') );
        DBMS_OUTPUT.PUT_LINE( 'addressType->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'addressType') );
        DBMS_OUTPUT.PUT_LINE( 'street2->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'street2') );
        DBMS_OUTPUT.PUT_LINE( 'street1->'||JSON_EXT.GET_STRING( JSON(LIST_VALUE.GET(I)) , 'street1') );
        DBMS_OUTPUT.PUT_LINE( '-------------------------------------------------' );    
    END LOOP;

END;

Result:

middleName->Wolfgang
firstName->Edward
lastName->Munster
-------------------------------------------------
city->
state->CA
addressType->home
street2->
street1->1313 Mockingbird Lane
-------------------------------------------------
city->
state->CA
addressType->business
street2->
street1->123 Morgan Rd.
-------------------------------------------------

How to use PL/JSON to parse array data

3 Answers3