Parse JSON array in PL/SQL
<p>PL/SQL newbie here.</p>

<p>I am using Oracle APEX as my REST server and I am sending a JSON array (<code>items</code>) from my app to the REST server. The JSON contains logs, of which there could be 100s. I can send them successfully one by one, but that's not efficient so I want to be able to send a JSON array containing the logs instead.</p>

<p>Here is some test JSON:</p>

<pre><code>{
    "items": [{
        "source": "00000999",
        "message": "test1"
    }, {
        "source": "00000999",
        "message": "test2"
    }, {
        "source": "00000999",
        "message": "test3"
    }, {
        "source": "00000999",
        "message": "test4"
    }]
}
</code></pre>

<p>Once I can parse that JSON array, I will then be adding them to the database as separate rows.</p>

<p>Here is what I have at the moment:</p>

<pre><code>set serveroutput on;
declare
items varchar2(2000):= '{"items":[{"source": "00000999","message": "test1"}, {"source": "00000999","message": "test2"}, {"source": "00000999","message": "test3"}, {"source": "00000999","message": "test4"}]}';
v_source varchar2(100);
   v_message varchar2(2000);
   v_json_list json_list;
   v_json_list2 json_list;
begin
   v_json_list := json_list(items);
   v_json_list2 := json_ext.get_string(json(v_json_list.GET(0)),'items');
   for i in 1..v_json_list2.count
      loop
         begin 
            v_source := json_ext.get_string(json(v_json_list2.GET(i)),'source');
            v_message := json_ext.get_string(json(v_json_list2.GET(i)),'message');
            ca_log_pak.log_info(v_source, v_message);
         end;
      end loop;
   commit;

   dbms_output.put_line('Y');


exception 
   when others then

      dbms_output.put_line(SQLERRM); 
end;
</code></pre>

<p>This is throwing an error saying <code>expression is of the wrong type</code> on <code>v_json_list := json_list(items);</code></p>

<p>Can someone show me how to parse the <code>items</code> array properly?</p>

<p>Thanks</p>