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:
CREATE TABLE view_count_sum (date_rw, buysum, totalsum) AS
SELECT 10, 1, 1 FROM DUAL UNION ALL
SELECT 9, 2, 2 FROM DUAL UNION ALL
SELECT 8, 3, 3 FROM DUAL UNION ALL
SELECT 7, 4, 4 FROM DUAL UNION ALL
SELECT 6, 5, 5 FROM DUAL UNION ALL
SELECT 5, 6, 6 FROM DUAL UNION ALL
SELECT 4, 7, 7 FROM DUAL UNION ALL
SELECT 3, 8, 8 FROM DUAL UNION ALL
SELECT 2, 9, 9 FROM DUAL UNION ALL
SELECT 1, 10, 10 FROM DUAL;
Then, if you do:
SELECT json_object(
'buy' VALUE JSON_ARRAYAGG(b.buysum),
'total' VALUE JSON_ARRAYAGG(b.totalsum)
) AS json
FROM (
select *
from view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;
Then the output is:
JSON {"buy":[1,2,3,4,5,6,7,8,9,10],"total":[1,10,9,8,7,6,5,4,3,2]}
Instead, if you add the ORDER BY clause into the aggregation functions:
SELECT json_object(
'buy' VALUE JSON_ARRAYAGG(b.buysum ORDER BY b.date_RW DESC),
'total' VALUE JSON_ARRAYAGG(b.totalsum ORDER BY b.date_RW DESC)
) AS json
FROM (
select *
from view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b;
Then the output is:
JSON {"buy":[1,2,3,4,5,6,7,8,9,10],"total":[1,2,3,4,5,6,7,8,9,10]}
db<>fiddle for Oracle 18