How can I get PL/JSON array of array value

Question

How can I retrieve content from below JSON using Pl/JSON? i need to fetch values of first array and second array

 {
  "documents1": [
    { 
      "key": "NATID",
      "validations": [
        {
          "error": "Field is required"
        }
      ]
    }
  ]
}

for example i have written the query as below which returns values of first array without any issue, but it doesn't return values of array of an array

DECLARE
   obj                     json
      := json
           ('{
  "documents1": [
    { 
      "key": "NATID",
      "validations": [
        {
          "error": "Field is required"
        }
      ]
    }
  ]
}'
           );
   list_value              json_list;
   list_value_validation   json_list;
   LIST_VALUES             json_list;
BEGIN
   list_value := json_list (obj.get ('documents'));

   FOR i IN 1 .. list_value.COUNT
   LOOP
      DBMS_OUTPUT.put_line (   'key->'
                            || json_ext.get_string (json (list_value.get (i)),
                                                    'key'
                                                   )
                           );
      ----------below scripts are not working
      list_value_validation := json_list (list_value.get ('values'));

      FOR iv IN 1 .. list_value_validation.COUNT
      LOOP
         DBMS_OUTPUT.put_line
                    (   'error->'
                     || json_ext.get_string
                                          (json (list_value_validation.get (iv)
                                                ),
                                           'error'
                                          )
                    );
      END LOOP;
   END LOOP;
END;

Sha · Accepted Answer · 2019-12-22 15:12:45Z

1

modified the code as below and it's working fine.

BEGIN
   list_value := json_list (obj.get ('documents'));

   FOR i IN 1 .. list_value.COUNT
   LOOP
         p_string_docs := list_value.get (i).TO_CHAR ();

         obj_docs := json (p_string_docs);

         l_output := json_ext.get_string (obj_docs, 'key');

         list_docs_validation := json_list (obj_docs.get ('values'));

            FOR i IN 1 .. list_docs_validation.COUNT
            LOOP

            l_output :=
               json_ext.get_string (obj_docs, 'key')|| ' | ' 
            || json_ext.get_string (json (list_docs_validation.get (i)),
                                    'code'
                                   );

               DBMS_OUTPUT.put_line (l_output);                         


            END LOOP;
   END LOOP;
END;

answered Dec 22, 2019 at 15:12

Sha

138 bronze badges

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

Collectives™ on Stack Overflow

How can I get PL/JSON array of array value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related