CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle12c
Vikash
Vikash

Reputation: 27

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

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.

Upvotes: 1

Views: 3576

Answers (2)

0xdb
0xdb

Reputation: 3697

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

Upvotes: 2

ismetguzelgun
ismetguzelgun

Reputation: 1105

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;
/

enter image description here

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.

Upvotes: 1

Related Questions