Oracle Cloud DB 19c bug with JSON_ARRAYAGG

Question

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

You have not asked a question. Have you filed a bug with MyOracle support? What version of Oracle 19 are you using and has it been updated to the latest patch? If there is a bug, what do you expect us to do about it? — MT0
– MT0, Commented Oct 6, 2021 at 15:57
Have you tried adding the ORDER BY clause into the JSON_ARRAYAGG function? It would look like JSON_ARRAYAGG(b.buysum ORDER BY b.buysum DESC) — Del
– Del, Commented Oct 6, 2021 at 16:01
See subject of bug where there id info about oracle version. No I haven't registered bug and not plan to waist my time on registration forms — koa73
– koa73, Commented Oct 6, 2021 at 17:52
As Del's comment and MT0's answer describe, you need to use the ORDER BY clause of JSON_ARRAYAGG. See documentation: docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/… — EJ Egyed
– EJ Egyed, Commented Oct 6, 2021 at 18:00
Is it a feature of JSON_ARRAYAGG or bug ? I think this decision is workaround isn't it — koa73
– koa73, Commented Oct 6, 2021 at 18:07

MT0 · Accepted Answer · 2021-10-06 16:09:46Z

2

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

answered Oct 6, 2021 at 16:09

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

koa73 Over a year ago

Thx a lot it's work for me in 19c Oracle too

Collectives™ on Stack Overflow

Oracle Cloud DB 19c bug with JSON_ARRAYAGG

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related