Using JSON_ARRAYAGG in a Sub-Select with ORDER BY gives error

Question

Using Oracle 19c:

I have the following query in which a Sub-Select (linked to the main Select via plans1_.ID) uses the JSON_ARRAYAGG function.

select
    ... , /* Other columns... */
    (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) RETURNING CLOB) 
        from mail_history_t mh 
        where mh.plan_id = plans1_.id and mh.is_current_status = 'Y' 
        /*---This is the problem block: If I remove this ORDER BY the query works---*/
        order by mh.sent_date desc 
    ) as col_33_0_, 
    /* ... */

    from TABLE_T table0_ 
    left outer join PLANS_T plans1_ 
        on table0_.SOME_ID=plans1_.SOME_ID
    where ... /* etc. */

When I have the order by as part of my select from mail_history_t mh, I get the error

00907. 00000 -  "missing right parenthesis"

But when I get rid of the order by clause, the query works. Also, the Sub-Select works by itself if I were to isolate it.

My goal is to obtain the JSON-Array representation of the rows with columns that satisfy the condition, but sorted by sent_date DESC.

Answer 1

JSON_ARRAYAGG() accepts its own ORDER BY clause:

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 RETURNING CLOB
              ) 

ORDER BY in subqueries is not recommended.