I try to use JSON_ARRAYAGG into select and get bug result with the same data
SELECT json_object(
'buy' VALUE JSON_ARRAYAGG(b.buysum),
'total' VALUE JSON_ARRAYAGG(b.totalsum)
)
FROM (
select *
from view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;
As a result i get JSON with 2 arrays which have decrease data order in first array and wrong order in second array
{"buy":[4168,4145,4130,4101,4068,4042,4008,3940,3900,3858],"total":[7778,7258,7333,7442,7546,7607,7642,7683,7718,7745]}
If I replace position JSON_ARRAYAGG in select I see right order for first array again and wrong order for second array
SELECT json_object(
'total' VALUE JSON_ARRAYAGG(b.totalsum),
'buy' VALUE JSON_ARRAYAGG(b.buysum)
)
FROM (
select *
from view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;
See result:
{"total":[7778,7745,7718,7683,7642,7607,7546,7442,7333,7258],"buy":[4168,3858,3900,3940,4008,4042,4068,4101,4130,4145]}
The order second and any other arrays is wrong. The first element is right but all other are reversed
Starting with some data where the expected order is easy to see:
Then, if you do:
Then the output is:
Instead, if you add the
ORDER BY
clause into the aggregation functions:Then the output is:
db<>fiddle for Oracle 18