OFFSET LIMIT (NoSQL query)

  • Article

APPLIES TO: NoSQL

The OFFSET LIMIT clause is an optional clause to skip and then take some number of values from the query. The OFFSET count and the LIMIT count are required in the OFFSET LIMIT clause.

When OFFSET LIMIT is used with an ORDER BY clause, the result set is produced by doing skip and take on the ordered values. If no ORDER BY clause is used, it results in a deterministic order of values.

Syntax

OFFSET <offset_amount> LIMIT <limit_amount>

Arguments

Description
<offset_amount> Specifies the integer number of items that the query results should skip.
<limit_amount> Specifies the integer number of items that the query results should include.

Examples

For the example in this section, this reference set of items is used. Each item includes a name property.

[
  {
    "name": "Sawyer Miller",
  },
  {
    "name": "Jennifer Wilkins",
  },
  {
    "name": "Hannah Haynes",
  },
  {
    "name": "Isaac Talbot",
  },
  {
    "name": "Riley Johnson",
  }
]

Note

In the original JSON data, the items are not sorted.

The first example includes a query that returns only the name property from all items sorted in alphabetical order.

SELECT VALUE {
    name: e.name
}
FROM
    employees e
WHERE
    e.team = "Leadership team"
ORDER BY
    e.name

[
  {
    "name": "Hannah Haynes"
  },
  {
    "name": "Isaac Talbot"
  },
  {
    "name": "Jennifer Wilkins"
  },
  {
    "name": "Riley Johnson"
  },
  {
    "name": "Sawyer Miller"
  }
]

This next example includes a query using the OFFSET LIMIT clause to skip the first item. The limit is set to the number of items in the container to return all possible remaining values. In this example, the query skips one item, and returns the remaining four (out of a limit of five).

SELECT VALUE {
    name: e.name
}
FROM
    employees e
WHERE
    e.team = "Leadership team"
ORDER BY
    e.name
OFFSET 1 LIMIT 5

[
  {
    "name": "Isaac Talbot"
  },
  {
    "name": "Jennifer Wilkins"
  },
  {
    "name": "Riley Johnson"
  },
  {
    "name": "Sawyer Miller"
  }
]

This final example includes a query using the OFFSET LIMIT clause to return a subset of the matching items by skipping one item and taking the next three.

SELECT VALUE {
    name: e.name
}
FROM
    employees e
WHERE
    e.team = "Leadership team"
ORDER BY
    e.name
OFFSET 1 LIMIT 3

[
  {
    "name": "Isaac Talbot"
  },
  {
    "name": "Jennifer Wilkins"
  },
  {
    "name": "Riley Johnson"
  }
]

Remarks

  • Both the OFFSET count and the LIMIT count are required in the OFFSET LIMIT clause. If an optional ORDER BY clause is used, the result set is produced by doing the skip over the ordered values. Otherwise, the query returns a fixed order of values.
  • The RU charge of a query with OFFSET LIMIT increases as the number of terms being offset increases. For queries that have multiple pages of results, we typically recommend using continuation tokens. Continuation tokens are a "bookmark" for the place where the query can later resume. If you use OFFSET LIMIT, there's no "bookmark." If you wanted to return the query's next page, you would have to start from the beginning.
  • You should use OFFSET LIMIT for cases when you would like to skip items entirely and save client resources. For example, you should use OFFSET LIMIT if you want to skip to the 1000th query result and have no need to view results 1 through 999. On the backend, OFFSET LIMIT still loads each item, including those items that are skipped. The performance advantage is measured in reducing client resources by avoiding processing items that aren't needed.

Related content