Oracle Cloud DB 19c bug with JSON

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 place 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

CodePudding user response：

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]}

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]}

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