Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JSON array index as a variable

InoLNov 25 2024

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.

Comments
Post Details
Added on Nov 25 2024
4 comments
488 views