I'm trying to use a variable as an array index, but can't get it to work (in Oracle 19c). Consider this example:
declare
v_json varchar2(32767) :=
q'[{
"applicants": [
{
"name": "John"
},
{
"name": "Jane"
}
]
}]';
begin
for r in (
select name
from json_table (
v_json
,'$.applicants[1]' columns (
name path '$.name'
)
)
)
loop
dbms_output.put_line(r.name);
end loop;
end;
I have a hard coded index in $.applicants[1]
but I would like to make the index a variable. Something like
$.applicants[$idx]
So far no luck in getting the correct syntax. Any help is very much appreciated.