Подзапрос с функцией JSON_ARRAYAGG и ORDER BY даёт ошибку: "ORA-00907: missing right parenthesis"

Question

Есть запрос, в котором подзапрос использует функцию JSON_ARRAYAGG:

select
    ... , /* другие колонки */
    (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' 
        /*---тут ошибка, если закомментировать ORDER BY, то запрос работает---*/
        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 ... /* условия */

С ORDER BY как часть select from mail_history_t mh получаю ошибку:

ORA-00907: missing right parenthesis

Но если закомментировать ORDER BY, то запрос работает. Также, подзапрос работает если его выделить как отдельный запрос.

Цель, получить массив JSON обьектов полученных из записей отвечающих условиям, но отсортированным по sent_date DESC.

_{Свободный перевод Using JSON_ARRAYAGG in a Sub-Select with ORDER BY gives error от участника @gene b}

Answer 1

Нет смысла сортировать скалярный подзапрос, поэтому ORDER BY в нём синтактически неверен.

Для сортировки JSON объектов в функции JSON_ARRAYAGG есть свой ORDER BY.

Воспроизводимый пример задачи как в вопросе:

create table t1 (id number);
create table t2 (id number, t1id number,  memo varchar2 (96), dt date);
insert all 
    into t1 values (1)
    into t2 values (1, 1, 'memo 1', date'2020-07-01')
    into t2 values (2, 1, 'memo 2', date'2020-07-02')
select 1 from dual;
    
select id, (
    select json_arrayagg (
        json_object ('dt' value t2.dt, 'memo' value t2.memo) 
        order by dt desc returning clob) 
    from t2 
    where t2.t1id = t1.id) json
from t1
/

   ID JSON
----- --------------------------------------------------------------------------------
    1 [{"dt":"2020-07-02T00:00:00","memo":"memo 2"},{"dt":"2020-07-01T00:00:00","memo"

БЛОГ НА HUSL

Подзапрос с функцией JSON_ARRAYAGG и ORDER BY даёт ошибку: "ORA-00907: missing right parenthesis"

Ответы (1 шт):