By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
10 rows affected
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;
| JSON |
|---|
| {"buy":[1,2,3,4,5,6,7,8,9,10],"total":[1,10,9,8,7,6,5,4,3,2]} |
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;
| JSON |
|---|
| {"buy":[1,2,3,4,5,6,7,8,9,10],"total":[1,2,3,4,5,6,7,8,9,10]} |