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;
TL;DR - You cannot as you have duplicate keys in an object.
- 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;
Since you're running Oracle 12c, you don't need Apex_json. You may use standard Oracle's JSON functions.
set serveroutput on
declare
sample_json varchar2 (32767)
:= '[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]';
BEGIN
for rec IN (
select j.name,j.year
from json_table(sample_json,'$[*]' COLUMNS
name varchar2(20) PATH '$.NAME',
year NUMBER PATH '$.YEAR'
) j )
LOOP
dbms_output.put_line (rec.name||','||rec.year);
END LOOP;
END;
/
AUDI,2000
BMW,2010
PL/SQL procedure successfully completed.
[{ "NAME":"AUDI" , "YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"], i.e an array of jsons. Then it becomes easier to parse it. In it's present state, the json functions are bound to parse the first key,value pair.{[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]}is invalid JSON as the outer{}does not have a key/value pair. You want something like{"data":[{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]}or just to remove the outer curly braces.