How to loop through a json string with PL/SQL?

Question

My example only returns BMW 2010. How do I get it to return AUDI 2000 and BMW 2010?

declare
    sample_json   varchar2 (32767)
        := '

            [{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]
';
begin
    apex_json.parse (sample_json);
    dbms_output.put_line (apex_json.get_varchar2 ('NAME'));
    dbms_output.put_line (apex_json.get_varchar2 ('YEAR'));
end;

Answer 1

TL;DR - You cannot as you have duplicate keys in an object.

---

From JSON Standard - RFC 7159

4. Objects

An object structure is represented as a pair of curly brackets surrounding zero or more name/value pairs (or members). A name is a string. A single colon comes after each name, separating the name from the value. A single comma separates a value from a following name. The names within an object SHOULD be unique.

(Added emphasis)

{"NAME":"AUDI","YEAR":"2000","NAME":"BMW","YEAR":"2010"}

While technically it is syntactically correct JSON, it does not make sense as you are duplicating keys so most (every) JSON parsers following RFC 7159 will overwrite the first instance of a key with later occurrences so your JSON is effectively:

{"NAME":"BMW","YEAR":"2010"}

And you cannot get AUDI/2000 from the output (unless you parse the JSON by hand).

If you want to send multiple values then you should use an array:

[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]

---

Update

You can try:

declare
  sample_json varchar2(32767) := '{"data":[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]}';
begin
  apex_json.parse (sample_json);
  dbms_output.put_line (apex_json.get_varchar2 ('data[1].NAME'));
  dbms_output.put_line (apex_json.get_varchar2 ('data[1].YEAR'));
  dbms_output.put_line (apex_json.get_varchar2 ('data[2].NAME'));
  dbms_output.put_line (apex_json.get_varchar2 ('data[2].YEAR'));
end;

or (if apex will accept an array as its outer object):

declare
    sample_json varchar2(32767) := '[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]';
begin
  apex_json.parse (sample_json);
  FOR i IN 1 .. 2 LOOP
    dbms_output.put_line (apex_json.get_varchar2(p_path=>'[%d].NAME',p0=>i));
    dbms_output.put_line (apex_json.get_varchar2(p_path=>'[%d].YEAR',p0=>i));
  END LOOP;
end;