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.

Answer 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.