New(ish) in Apache Oak: Query Offset / Limit Options

I had the opportunity to work with the Oak indexing team this year and took the opportunity scratch an itch I've had for some time: adding support for offset and limit options in any Oak query including JCR-SQL2 and xpath.

The new offset and limit options are available in Apache Jackrabbit Oak Search v1.44.0 which is included in Adobe Experience Manager as a Cloud Service release 8182+.

To use the option, add the limit and offset options in the end of the query, for example if I wanted to get the first 10 nt:file nodes under /content/dam:

# JCR-SQL2
SELECT * FROM [nt:file] AS s WHERE ISDESCENDANTNODE([/content/dam]) OPTION(LIMIT 10)

# xpath
/jcr:root/content/dam//element(*,nt:file) option(limit 10)

Now, you might be asking, can't I already set the limit and offset in the javax.jcr.query.Query object? You certainly can if you have access to the Query object... but what if you wanted to use an API or a UI that doesn't allow you to access the Java Query object directly.

As I discussed in Demystifying Oak Search Part 2: Traversal, you should always set a limit on queries, even when writing a test query in CRXDE. 

To page through results, you can use the offset option as well:

SELECT * FROM [nt:file] AS s WHERE ISDESCENDANTNODE([/content/dam]) OPTION(LIMIT 10, OFFSET 10)

However to calculate the offset, Oak needs to read the nodes until it reaches the offset. This can cause major performance / memory issues for large offsets and even trigger a traversal error. Instead, the recommended approach to page large result sets is to use keyset pagination.

Hope you find the new feature as useful as I have. Happy querying!