Oracle JSON_ARRAYAGG with Limit/Rownum based on ORDER BY

Question

I'm using Oracle 19c and the JSON_ARRAYAGG function (with JSON_OBJECT) to return a concatenated array string of JSON objects. I need to limit the result to top 10 objects based on the ORDER BY SENT_DATE DESC.

Note that JSON_ARRAYAGG has its own ORDER BY so that's where I put it. However, is there a Limit facility?

The following is syntactically correct, but the results are incorrect. My JSON objects are not in SENT_DATE DESC order in the concatenated string.

SELECT json_arrayagg(json_object('sentDate' value mh.sent_date, 
                                 'sentByEmail' value mh.send_by_email,  
                                 'sentBy' value mh.sent_by, 
                                 'sentByName' value mh.sent_by_name,  
                                 'sentToEmail' value mh.sendee_email)  
                                 ORDER BY mh.sent_date DESC) /*ORDER BY inside json_arrayagg)*/
                                                             /*Normally this works, but not with ROWNUM*/
    from mail_history_t mh 
    where mh.plan_id = 763 and mh.is_current_status = 'Y' and rownum <= 10; /*ROWNUM outside*/

I see that it's incorrect if I check the top results in my usual row query,

select * from mail_history_t where plan_id = 763 and is_current_status ='Y' order by sent_date desc;

GMB · Accepted Answer · 2020-07-26 22:47:13Z

4

You can select the top 10 rows in a subquery first, using the fetch first row-limiting clause, then aggregate in the outer query:

select json_arrayagg(
    json_object(
        'sentDate'    value sent_date, 
        'sentByEmail' value send_by_email,  
        'sentBy'      value sent_by, 
        'sentByName'  value sent_by_name,  
        'sentToEmail' value sendee_email
    )  
    order by sent_date desc
) js_array
from (
    select *
    from mail_history_t
    where plan_id = 763 and  is_current_status = 'Y'
    order by sent_date desc
    fetch first 10 rows only
) t

edited Jul 26, 2020 at 22:47

answered Jul 26, 2020 at 22:44

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

gene b. Over a year ago

Thanks, is the JSON_AGGARRAY's internal ORDER BY still necessary after this?

GMB Over a year ago

@geneb.: it does a different thing, that is ordering the elements within the json array (which is usually a good thing if you want a deterministic results - otherwise the database is free to order the array elements as it likes).

Collectives™ on Stack Overflow

Oracle JSON_ARRAYAGG with Limit/Rownum based on ORDER BY

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related