Skip to Main Content
  • Questions

  • Why is json_array_t using 0-based indexing

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dieter.

Asked: April 30, 2024 - 7:17 am UTC

Last updated: May 01, 2024 - 5:38 pm UTC

Version: Oracle Database 19c

Viewed 1000+ times

You Asked

It took me 25 years to get used to Oracle using 1-based indexing in pretty much all API's.
How the rather new json_array_t data structure used a 0-based indexing and drives me crazy.
Is there any reason behind this "strange anomaly" or did someone just want to drive people crazy?

The following example only returns 2 and 3 because it must be written "FOR i IN 0 .. c_json.get_size() - 1 LOOP ":

DECLARE 
 c_json CONSTANT json_array_t := json_array_t('[1, 2, 3]'); 
BEGIN 
    FOR i IN 1 .. c_json.get_size() LOOP 
     dbms_output.put_line(c_json.get_number(i)); 
    END LOOP; 
END;
/


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/bnxr70bqgx501bo3mmfcvo5cw

and Chris said...

JSON arrays are zero-indexed, so this more closely matches the JSON standard (use 21c or higher for the JSON data type):

DECLARE 
  c_json CONSTANT json := json('[1, 2, 3]'); 
BEGIN 
  dbms_output.put_line ( json_value ( c_json, '$[0]' ) );
  dbms_output.put_line ( json_value ( c_json, '$[1]' ) );
  dbms_output.put_line ( json_value ( c_json, '$[2]' ) );
  dbms_output.put_line ( nvl ( json_value ( c_json, '$[3]' ), 'N/A' ) );
END;
/
1
2
3
N/A

Rating

  (1 rating)

Comments

Json is a data format

Dieter Oberkofler, April 30, 2024 - 3:58 pm UTC

In my understanding json is “just” a data format and not an api definition on how to access it.
The json types jn Oracle are a PL/SQL api to work with json.
I understand that the close relationship to a javascript (using 0-based indexing) object can be seen as a reason, but what would be advantages in a programming language like pl/sql that exclusively uses 1-based indexing?
Chris Saxon
May 01, 2024 - 5:38 pm UTC

pl/sql that exclusively uses 1-based indexing

Well you can do something like this, so I wouldn't say PL/SQL exclusively uses 1-based indexing:

declare
  type arr is table of number 
    index by pls_integer; 

  a arr := arr (0 => 1, 1 => 2);
begin
  for inx in 0 .. a.count - 1 loop
    dbms_output.put_line ( inx || ' = ' || a ( inx ) );
  end loop;
end;
/
0 = 1
1 = 2


Though this is only for associative arrays, not nested tables or varrays.

JSON_ARRAY_T isn't an array type - it's a subtype of the JSON_ELEMENT_T object. All other implementations of JSON use zero-based indexing, so ultimately it was decided that JSON_ARRAY_T would use 0-based indexing.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database