Oracle JSON_ARRAYAGG with Limit/Rownum

user_1005607 0
Viewed 2558

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;
oracle19c oracle arrays sql json
1 Answers

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
user_106767160
Related