Oracle JSON_ARRAYAGG with Limit/Rownum based on ORDER BY

In this article, we will explore the use of JSON_ARRAYAGG in Oracle databases to concatenate arrays of JSON objects. We will also delve into a specific scenario where limiting the result set requires using ROWNUM or FETCH FIRST clause. Additionally, we will examine how to use these clauses effectively to achieve our desired outcome.

Understanding JSON_ARRAYAGG

JSON_ARRAYAGG is an Oracle database function that allows you to concatenate arrays of JSON objects into a single array string. This can be useful in various scenarios such as data aggregation, filtering, and sorting.

Here’s a basic example of how JSON_ARRAYAGG works:

SELECT json_arrayagg(json_object('key' value 'value'))
       FROM dual;

This will return the following JSON array string: [{"key":"value"}].

Using ORDER BY with JSON_ARRAYAGG

When using ORDER BY with JSON_ARRAYAGG, it is applied to the individual JSON objects within the aggregation. This means that the resulting array will be sorted based on the specified column.

Here’s an example:

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)
       FROM mail_history_t mh;

This will return the JSON array string sorted by sentDate in descending order.

Limiting Results with ROWNUM

In Oracle databases, you can use the ROWNUM pseudocolumn to limit the number of rows returned from a query. However, using ROWNUM with JSON_ARRAYAGG has some limitations.

Here’s an example that uses ROWNUM:

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)
       FROM mail_history_t mh
       WHERE rownum <= 10;

This will return the top 10 rows in descending order of sentDate. However, as noted in the original question, this approach can be unreliable due to Oracle’s handling of ordering and filtering within nested queries.

Limiting Results with FETCH FIRST

Oracle Database 12c introduced a new clause called FETCH FIRST that allows you to specify a limit on the number of rows returned from a query. This can be used in conjunction with ORDER BY to achieve similar results to using ROWNUM.

Here’s an example that uses FETCH FIRST:

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)
       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;

This will return the top 10 rows in descending order of sentDate. The use of FETCH FIRST provides a more reliable and efficient way to limit results compared to using ROWNUM.

Best Practices

When working with JSON_ARRAYAGG, it’s essential to consider the following best practices:

• Use ORDER BY within the aggregation function to ensure that the resulting array is sorted as expected.
• When limiting results, use FETCH FIRST instead of ROWNUM for more reliable and efficient results.
• Avoid using complex subqueries or joins with JSON_ARRAYAGG, as this can lead to performance issues.

Conclusion

In this article, we explored how to limit the result set of a JSON_ARRAYAGG query in Oracle databases. We examined two approaches: using ROWNUM and using FETCH FIRST. While both methods have their limitations, FETCH FIRST provides a more reliable and efficient way to achieve our desired outcome. By following best practices and considering the nuances of Oracle’s JSON functions, you can effectively use JSON_ARRAYAGG in your database queries.

Last modified on 2023-12-11