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

Error is self descriptive. "Wrong type"

Avrajit Roy
– Avrajit Roy

2016-04-21 14:25:05 +00:00
Commented Apr 21, 2016 at 14:25 — Avrajit Roy
– Avrajit Roy, Commented Apr 21, 2016 at 14:25
Could you suggest a type I can use, instead of json_list?

eoinzy
– eoinzy

2016-04-21 14:34:55 +00:00
Commented Apr 21, 2016 at 14:34 — eoinzy
– eoinzy, Commented Apr 21, 2016 at 14:34

Cristian_I · Accepted Answer · 2016-04-21 20:41:03Z

3

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;

edited Apr 21, 2016 at 20:41

answered Apr 21, 2016 at 14:37

Cristian_I

1,6251 gold badge13 silver badges17 bronze badges

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

2 Comments

eoinzy Over a year ago

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 Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:31:12Z

0

did you try to use apex_json package in 5.0 by using it you can convert your data to an adhoc xmltable and insert into your permanent table with INSERT INTO SELECT concept. you can see the sample of apexjson and xml table at

Parse JSON object to use in Apex item select list

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 24, 2016 at 14:19

Bahadirs

1881 silver badge5 bronze badges

Collectives™ on Stack Overflow

Parse JSON array in PL/SQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related