If this should be in ORDS, SODA & JSON forum, please move, but it really about arrays - just happens to use JSON.
version = Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production (autonomous)
-- wrong / unexpected sort order
with v1 as (select json_array(4,2) as ja1 union all select json_array(4,2,5) union all select json_array(4,1,5)) select v1.ja1 from v1 order by ja1 ;
JA1
-------------------------
[4,1,5]
[4,2,5]
[4,2]
-- correct / expected sort order
with v1 as (select json[4,2] as ja1 union all select json[4,2,5] union all select json[4,1,5]) select v1.ja1 from v1 order by ja1 ;
JA1
-------------------------
[4,1,5]
[4,2]
[4,2,5]
I am brand new to JSON but believe above contradicts documentation - please let me know if I am misreading results/docs.
e.g
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-in-oracle-database.html#GUID-76324651-AD22-4110-9463-98DBBE441471
Two arrays are sorted by comparing their elements, in order.
When two corresponding elements are unequal, the sort order of those elements determines the order of the two arrays.
For example, with ascending sort order [4, 2, 5] sorts before [4, 2, 9] because 5 sorts before 9.
If all elements of one array are equal to the corresponding elements of a longer array, the shorter array sorts before the longer one. For example, with ascending sort order [4, 2] sorts before [4, 2, 5], but it sorts after [4, 1, 5].
and
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/generation.html#GUID-076541BA-F6D6-41B4-828D-FE5593AC6E54
When employed as an alternative syntax for json_object or json_array, you follow constructor JSON directly with braces ({}) and brackets ([]), respectively, for object and array generation, instead of the usual parentheses (()).