Oracle JSON_ARRAYAGG с лимитом/Rownum

Я использую Oracle 19c и функцию JSON_ARRAYAGG (с JSON_OBJECT) для возврата объединенной строки массива объектов JSON. Мне нужно ограничить результат 10 лучшими объектами на основе ORDER BY SENT_DATE DESC.

Обратите внимание, что у JSON_ARRAYAGG есть свой собственный ORDER BY, так что я поместил его туда. Однако есть ли лимит?

Следующее синтаксически верно, но результаты неверны. Мои объекты JSON не находятся в порядке SENT_DATE DESC в объединенной строке.

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*/

Я вижу, что это неправильно, если я проверяю лучшие результаты в своем обычном запросе строки,

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

arrays json sql oracle oracle19c
person gene b.    schedule 26.07.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете сначала выбрать первые 10 строк в подзапросе, используя предложение ограничения строки fetch first, а затем агрегировать во внешнем запросе:

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
person GMB    schedule 26.07.2020
comment
Спасибо, а внутренний ORDER BY JSON_AGGARRAY все еще нужен после этого? - person gene b.; 27.07.2020
comment
@geneb.: он делает другую вещь, то есть упорядочивает элементы внутри массива json (что обычно хорошо, если вы хотите получить детерминированные результаты, иначе база данных может упорядочивать элементы массива как нравится). - person GMB; 27.07.2020