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

Question

I am facing an issue when my json response is like this:

declare
sample_json   varchar2 (32767)
        := '{"items": [{"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 '$.items[0].NAME',
        year NUMBER       PATH '$.items[0].YEAR'
       ) j  ) 
     LOOP
       dbms_output.put_line (rec.name||','||rec.year);
     END LOOP;
END;
/

I have to use $.items[0].NAME to get one value but i want to make it dynamic so that it can loop to give me both the values.

0xdb · Accepted Answer · 2020-09-07 23:43:10Z

2

It's also posible to parse in pure PL/SQL. Consider the following reproducible example:

declare
    jdoc varchar2 (32767) := 
        '{"items": [{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]}';
    jobj json_object_t;
    keys json_key_list;
    jarr json_array_t;
begin
    jobj := json_object_t (jdoc);
    keys := jobj.get_keys;
    jarr := jobj.get_array (keys(1));
    for ai in 0..jarr.get_size ()-1 loop
        jobj := treat (jarr.get(ai) as json_object_t);
        keys := jobj.get_keys;
        for ki in 1..keys.count loop
            dbms_output.put_line (keys(ki)||'='||jobj.get_string (keys(ki)));
        end loop;
    end loop;
end;
/
PL/SQL procedure successfully completed.

NAME=AUDI
YEAR=2000
NAME=BMW
YEAR=2010

answered Sep 7, 2020 at 23:43

0xdb

3,7071 gold badge23 silver badges39 bronze badges

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

Comments

ismetguzelgun · Accepted Answer · 2020-05-29 11:55:20Z

1

I can not fully understand your needs about making your query dynamic but maybe below script can give you some idea about using JSON_VALUE and writing dynamic plsql statements.

DECLARE
   SAMPLE_JSON     VARCHAR2 (32767)
      := '{"items": [{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"}]}';
   V_RESULT_NAME   VARCHAR2 (500);
   V_RESULT_YEAR   VARCHAR2 (500);
   V_KEY           VARCHAR (200) := 'items[0].NAME';
   V_KEY2          VARCHAR (200) := 'items[0].YEAR';
BEGIN
   EXECUTE IMMEDIATE 'SELECT JSON_VALUE(:1,''$.' || V_KEY || ''')  FROM DUAL'
      INTO V_RESULT_NAME
      USING SAMPLE_JSON;

   DBMS_OUTPUT.PUT_LINE (V_RESULT_NAME );
END;
/

Here you can dynamically insert values from json data into your table.

DECLARE
SAMPLE_JSON   VARCHAR2 (32767)
        := '{"items": [{"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 '$.items[0].NAME',
        YEAR NUMBER       PATH '$.items[0].YEAR'
       ) J  ) 
     LOOP
     EXECUTE IMMEDIATE 'INSERT INTO YOUR_TABLE(NAME,YEAR) VALUES('||REC.NAME||','||REC.YEAR||'); COMMIT;'
--       DBMS_OUTPUT.PUT_LINE (REC.NAME||','||REC.YEAR);
     END LOOP;
END;
/

Without using index.

DECLARE
SAMPLE_JSON   VARCHAR2 (32767)
        := '{"items": [{"NAME":"AUDI","YEAR":"2000"},{"NAME":"BMW","YEAR":"2010"},{"NAME":"TOFAS","YEAR":"1995"}]}';
BEGIN
FOR REC IN (
     SELECT J.NAME,J.YEAR 
       FROM JSON_TABLE(SAMPLE_JSON,'$.items[*]' COLUMNS 
        NAME VARCHAR2(20) PATH '$.NAME',
        YEAR NUMBER       PATH '$.YEAR'
       ) J  ) 
     LOOP
     --EXECUTE IMMEDIATE 'INSERT INTO YOUR_TABLE(NAMEE,YEARR) VALUES('||REC.NAME||','||REC.YEAR||'); COMMIT;';
      DBMS_OUTPUT.PUT_LINE (REC.NAME||','||REC.YEAR);
     END LOOP;
END;
/

Note: If you want to dynamically describe columns from your json data as i know of oracle can not do that. You have to describe json tags one by one as JSON_TABLE columns before use them.

edited May 29, 2020 at 11:55

answered May 29, 2020 at 10:31

ismetguzelgun

1,1058 silver badges16 bronze badges

4 Comments

Vikash Over a year ago

The requirement basically is to loop through the json response so that we can insert into the database table. Above is just the sample example we can have 10-20 json object in response and we need to loop so that we get all the NAME,YEAR column in our database table.

ismetguzelgun Over a year ago

But your question has already a great way for looping through json data. Why can't you just use you already had ? Check out i edited my answer. @Vikash

Vikash Over a year ago

Yes but this will only give me output as AUDI,2000. As the path has hard coded index $.items[0].NAME. So imagine for 10 such records ? I want a way where i can get all the values.

ismetguzelgun Over a year ago

Then Why are you perseveringly trying to use [0]. You can achieve your goal with my third edit. Check that out. @Vikash

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related