I'm only able to access values in the outer JSON array , is there a way of accessing the values of the inner JSON array in Oracle plsql?

Question

[
  {
    "name": "sometablename",
    "numofcolumns": 5,
    "numofrows": 5,
    "colheadings": [
      {
        "colname": "customcol1",
        "coltype": "number"
      },
      {
        "colname": "customcol2",
        "coltype": "varchar2"
      },
      {
        "colname": "customcol3",
        "coltype": "varchar2"
      },
      {
        "colname": "customcol4",
        "coltype": "varchar2"
      },
      {
        "colname": "customcol5",
        "coltype": "number"
      }
    ]
  }
]

Answer 1

The first issue you will want to correct is that your JSON is invalid. There is an extra comma after the closing bracket of your colheadings array.

If you are using SQL, you can get the values of the inner JSON array just by specifying the path.

WITH
    sample_json
    AS
        (SELECT '[{ "name": "sometablename", "numofcolumns": 5, "numofrows": 5, "colheadings": [{ "colname": "customcol1", "coltype": "number" }, { "colname": "customcol2", "coltype": "varchar2" }, { "colname": "customcol3", "coltype": "varchar2" }, { "colname": "customcol4", "coltype": "varchar2" }, { "colname": "customcol5", "coltype": "number" } ], }]'    AS json_text
           FROM DUAL)
SELECT json_value (s.json_text, '$[0].name')                       AS outer_name,
       json_value (s.json_text, '$[0].colheadings[0].colname')     AS colname1,
       json_value (s.json_text, '$[0].colheadings[0].coltype')     AS coltype1,
       json_value (s.json_text, '$[0].colheadings[1].colname')     AS colname2,
       json_value (s.json_text, '$[0].colheadings[1].coltype')     AS coltype2
  FROM sample_json s;

If you are using PL/SQL, you will need to continually get the nested objects/arrays until you are at the level that contains the information you are trying to retrieve. See the example below (after correcting the invalid JSON):

DECLARE
    l_outer_array        json_array_t
        := json_array_t (
               '[{ "name": "sometablename", "numofcolumns": 5, "numofrows": 5, "colheadings": [{ "colname": "customcol1", "coltype": "number" }, { "colname": "customcol2", "coltype": "varchar2" }, { "colname": "customcol3", "coltype": "varchar2" }, { "colname": "customcol4", "coltype": "varchar2" }, { "colname": "customcol5", "coltype": "number" } ] }]');
    l_outer_object       json_object_t := TREAT (l_outer_array.get (0) AS json_object_t);
    l_col_headings       json_array_t := l_outer_object.get_array ('colheadings');
    l_col_heading_info   json_object_t;
BEGIN
    FOR i IN 0 .. l_col_headings.get_size - 1
    LOOP
        l_col_heading_info := TREAT (l_col_headings.get (i) AS JSON_OBJECT_T);
        DBMS_OUTPUT.put_line ('Column ' || (i + 1));
        DBMS_OUTPUT.put_line ('---colname : ' || l_col_heading_info.get_string ('colname'));
        DBMS_OUTPUT.put_line ('---coltype : ' || l_col_heading_info.get_string ('coltype'));
    END LOOP;
END;
/

Update

To use APEX_JSON, you will need to specify the full path of the key that want to get the data for. Below is an example of how to loop through all the "colheading" objects. The get_count function returns the number of items in the array so it knows how many times to loop.

DECLARE
    j   apex_json.t_values;
BEGIN
    apex_json.parse (
        p_values   => j,
        p_source   =>
            '[{ "name": "sometablename", "numofcolumns": 5, "numofrows": 5, "colheadings": [{ "colname": "customcol1", "coltype": "number" }, { "colname": "customcol2", "coltype": "varchar2" }, { "colname": "customcol3", "coltype": "varchar2" }, { "colname": "customcol4", "coltype": "varchar2" }, { "colname": "customcol5", "coltype": "number" } ] }]');

    DBMS_OUTPUT.put_line (apex_json.get_varchar2 (p_path => '[%d].name', p_values => j, p0 => 1));
    DBMS_OUTPUT.put_line (apex_json.get_count (p_path => '[%d].colheadings', p_values => j, p0 => 1));

    FOR i IN 1 .. apex_json.get_count (p_path => '[%d].colheadings', p_values => j, p0 => 1)
    LOOP
        DBMS_OUTPUT.put (apex_json.get_varchar2 (p_path     => '[%d].colheadings[%d].colname',
                                                 p_values   => j,
                                                 p0         => 1,
                                                 p1         => i));

        DBMS_OUTPUT.put_line (   '---'
                              || apex_json.get_varchar2 (p_path     => '[%d].colheadings[%d].coltype',
                                                         p_values   => j,
                                                         p0         => 1,
                                                         p1         => i));
    END LOOP;
END;
/

Output

sometablename
5
customcol1---number
customcol2---varchar2
customcol3---varchar2
customcol4---varchar2
customcol5---number