CODEWITHSUNDEEP
oracle-databaseplsqloracle12c
Pointer
Pointer

Reputation: 2186

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

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;

Upvotes: 0

Views: 5474

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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.

Upvotes: 5

MT0
MT0

Reputation: 167774

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

From JSON Standard - RFC 7159

  1. 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;

Upvotes: 5

Related Questions