Oracle SQL JSON nested loop

This is a very cool way to do a depth first traversal of JSON that I have test in the Oracle 19c version of the database

So the cool part is

col3 clob format json with wrapper path '$.col3[*]'

that stores a JSON objects for col3 so that the outer loop does not care what the object contents are
the inner loop then parses this JSON.

This is a very compact and readable way of traversing all of your JSON

begin

for rec1 in

(select col1, col2, col3

from json_table('{"col1": 1, "col2" : 2, col3 : {"col3foo1" :4, "col3foo2": 5}}', '$[*]'

columns

col1 varchar2(200) path '$.col1',

col2 varchar2(200) path '$.col2',

col3 clob format json with wrapper path '$.col3[*]'

) )

loop

dbms_output.put_line('col1=' || rec1.col1 || ' col3=' || rec1.col3);

for rec2 in (select col3foo1 from json_table(rec1.col3, '$[*]'

columns col3foo1 varchar2(200) path '$.col3foo1'))

loop

dbms_output.put_line(' col3foo1=' || rec2.col3foo1 );

end loop;

end loop;

end;