Pagination and sorting
Sorting and pagination commands in J1QL, such as ORDER BY
, SKIP
, and LIMIT
, provide efficient ways to organize and navigate query results. By utilizing ORDER BY
, you can sort entities based on specific fields, while SKIP
allows you to skip a certain number of results, and LIMIT
determines the maximum number of results to be returned. These commands enable fine-grained control over result presentation and facilitate targeted analysis of data.
That by default, queries will return up to 250 results if no LIMIT
is specified.
In the example below, the query sorts users by their username and returns the 11th-15th users from the sorted list:
FIND Person WITH manager = undefined as u
ORDER BY u.username SKIP 10 LIMIT 5
Ascending and Descending Order
By default, ORDER BY
sorts in ascending order (ASC). You can explicitly specify the sort direction using ASC
or DESC
.
Ascending Order (Default)
The following query sorts AWS IAM users by creation date in ascending order (oldest to newest):
FIND aws_iam_user AS u
ORDER BY u.createdOn
LIMIT 10
Explicit Ascending Order
You can explicitly specify ascending order:
FIND aws_iam_user AS u
ORDER BY u.createdOn ASC
LIMIT 10
Descending Order
To sort in descending order (newest to oldest):
FIND aws_iam_user AS u
ORDER BY u.createdOn DESC
LIMIT 10
Multiple Sort Fields
You can sort by multiple fields with different directions:
FIND User AS u
ORDER BY u.active DESC, u.username ASC
LIMIT 20
This query first sorts users by active status (active users first), then sorts by username alphabetically within each status group.
Ordering by Aggregation Results
For aggregate queries, use the following pattern to sort results:
FIND User AS u
RETURN u.username, u.mfaEnabled
ORDER BY u.mfaEnabled DESC
LIMIT 10
This returns users sorted by their MFA status (enabled users first), which is useful for compliance reports.