Skip to main content

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.

note

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.