Parse JSON array in PL/SQL

Question

PL/SQL newbie here.

I am using Oracle APEX as my REST server and I am sending a JSON array (items) 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.

Here is some test JSON:

{
    "items": [{
        "source": "00000999",
        "message": "test1"
    }, {
        "source": "00000999",
        "message": "test2"
    }, {
        "source": "00000999",
        "message": "test3"
    }, {
        "source": "00000999",
        "message": "test4"
    }]
}

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

Here is what I have at the moment:

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;

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

Can someone show me how to parse the items array properly?

Thanks

Solution

I think your JSON string should be like this:

[{"source": "00000999","message": "test1"}, {"source": "00000999","message": "test2"}, {"source": "00000999","message": "test3"}, {"source": "00000999","message": "test4"}]

Try this:

DECLARE
  items VARCHAR2(2000):= '{"items":[{"source": "00000999","message": "test1"}, {"source": "00000999","message": "test2"}, {"source": "00000999","message": "test3"}, {"source": "00000999","message": "test4"}]}';
  my_json json_list   := json_list(LTRIM(RTRIM(items,'}'), '{"items":'));
  listElement json_value;
  jsonObj json;
BEGIN
  FOR i IN 1..my_json.count
  LOOP
    listElement := my_json.get(i);
    jsonObj     := json(listElement);
    dbms_output.put_line(jsonObj.json_data(1).mapname);
    dbms_output.put_line(jsonObj.json_data(1).str);
  END LOOP;
END;

Comments

[eoinzy]

Is it possible using the existing JSON I have? It is being auto-generated from the client side and I think the effort to change it on the client is more than if there's a simple fix on the server side. Thanks.

[Cristian_I]

What If you remove the extra text from the beginning and end of string? Something like LTRIM(RTRIM(<source>,'}'), '{"items":') I have edited my answer.