skip to content

System Query Options

5 roadmaps6 questions

$filter, $select and nested $expand, with ordering, paging and $apply aggregation, let a client build its query in the URL. Interviewers probe cost: an open query language invites expensive queries.

part ofAPI stylesoverview, primer and where to startread it →
on this pageshow

explore

questions

6

Given the OData 4.01 request `GET Customers?$filter=City eq 'Lyon'&$orderby=Name&$top=10&$skip=20&$count=true`, what comes back, and in what order are the options applied?

level: juniorimportance: must knowfreq 34%

answer

  1. order fixed by the specification
  2. filter, then count, then sort
  3. skip always before top
  4. count ignores the paging options

basics

~20 s

Customers 21 to 30 of those in Lyon, sorted by Name, plus the total number of Lyon customers. The service evaluates $filter, then $count, then $orderby, $skip and $top, whatever their order in the URL.

solid answer

~40 s

OData 4.01 says the result MUST be as if the options were evaluated in a fixed order: `$filter` narrows the set to Lyon customers, `$count=true` counts that filtered set, `$orderby=Name` sorts it (ascending when no direction is given), `$skip=20` drops the first twenty and `$top=10` keeps the next ten. The order in the query string does not matter — `$skip` is applied before `$top` even when written after it. The count ignores `$top`, `$skip` and `$expand`, so it reports every matching customer across all pages; in a 4.01 JSON response it appears as `@count` beside the `value` array (`@odata.count` in a 4.0 response). The service may still return fewer than ten rows with a next link if it pages the result itself.

go deeper

for a junior

Recall what each of the five options does and that the URL order is irrelevant: filter, count, sort, skip, then top.

for a middle

Explain why the count ignores $top and $skip, why $skip always precedes $top, and how @count sits beside the value array in a 4.01 JSON response.

for a senior

Bring up the stable-ordering requirement for positional paging, the possibly inexact count, and the fact that server-driven paging can still shorten the page.

for a principal

Weigh what an exact total count costs on a large filtered set, and whether the service should advertise that counting is not supported rather than compute it on every request.

## What each option asks for OData's **system query options** are query-string parameters that shape the collection a URL identifies. The request in the question uses five of them against the `Customers` entity set: | Option | Value here | Effect | |---|---|---| | `$filter` | `City eq 'Lyon'` | keeps only items for which the Boolean expression is true | | `$orderby` | `Name` | sorts the items; ascending unless `desc` is given | | `$skip` | `20` | excludes the first 20 items of the queried collection | | `$top` | `10` | returns at most 10 items | | `$count` | `true` | adds the total number of matching items to the response | String literals sit in single quotes, and `eq` is one of the comparison operators (`eq`, `ne`, `gt`, `ge`, `lt`, `le`), alongside `and`, `or`, `not` and, from 4.01, `in`. ## The evaluation order the specification fixes The OData 4.01 Protocol (section 11.2.1) says the result **MUST be as if** the options were evaluated in this order, regardless of how they are written in the URL: 1. `$schemaversion`, first of all, because it can change everything that follows. 2. Before any server-driven paging: `$apply`, `$compute`, `$search`, `$filter`, `$count`, `$orderby`, `$skip`, `$top`. 3. After server-driven paging: `$expand`, `$select`, `$format`. "As if" matters: a service may run one combined database query, but the answer must match this sequence. The protocol repeats the rule for paging explicitly: where `$top` and `$skip` are used together, `$skip` MUST be applied before `$top`, regardless of their order in the request. ## Walking through the request - `$filter=City eq 'Lyon'` reduces the customers to those in Lyon — say 57 of them. - `$count=true` counts that filtered set: 57. The count includes only results matching `$filter` and `$search`, and it **ignores `$top`, `$skip` and `$expand`**, so it is the total across all pages. - `$orderby=Name` sorts the 57 by name. Nulls sort before non-null values in ascending order, and `false` sorts before `true`. - `$skip=20` drops positions 1–20; `$top=10` keeps positions 21–30. So the client receives ten customers (positions 21 to 30) and the number 57. ## What the response looks like In OData 4.01 JSON, control information is written without the `odata.` infix, so the count is `@count`; a response with `OData-Version: 4.0` writes `@odata.count`. ```json { "@context": "https://api.example.com/odata/$metadata#Customers", "@count": 57, "value": [ { "ID": "C021", "Name": "Arnaud Fils", "City": "Lyon" }, { "ID": "C022", "Name": "Bertin SA", "City": "Lyon" } ] } ``` (The array is shortened; it would hold ten customers.) ## Details that trip candidates - **Stable ordering.** If no unique ordering is imposed through `$orderby`, the service MUST impose a stable ordering across requests that include `$top` or `$skip`. Sorting only by `Name` is not unique, so a careful client adds a key: `$orderby=Name,ID`. - **The count can be approximate.** The protocol warns that the inline count may not exactly equal the items a client can enumerate, because of latency between counting and reading, or inexact calculation on the service. - **Invalid values.** `$count` takes only `true` or `false`; any other value gets `400 Bad Request`. `$count=false`, or no `$count`, hints that the service SHOULD NOT return a count. - **Names and casing.** 4.01 services MUST accept system query option names case-insensitively and with or without the `$` prefix; clients that must also work with 4.0 services use lower-case names with `$`. - **No repeats.** The same system query option MUST NOT appear more than once for a resource. - **Server-driven paging still applies.** Paging sits between `$top` and `$expand` in the order, so a service may split even a ten-item result and return a next link. ## The $filter expression language in brief `$filter` takes a full Boolean expression, not just equality, which is why OData URLs can replace many bespoke endpoints: - **Comparison and logical operators:** `Amount ge 100 and (City eq 'Lyon' or City eq 'Nice')`, `not endswith(Email,'.test')`. - **Membership (4.01):** `City in ('Lyon','Nice')` is true if the left operand is a member of the right-hand list. - **String functions:** `contains(Name,'bio')`, `startswith(Name,'Ar')`, `endswith`, `tolower`, `toupper`, `trim`, `length`, `indexof`, `substring`, and from 4.01 `matchesPattern`. - **Date and time functions:** `year(CreatedAt) eq 2026`, `month`, `day`, `date`, `now()`. - **Arithmetic:** `add`, `sub`, `mul`, `div`, `mod`, and from 4.01 `divby`. Items for which the expression is false or null are left out of the response. ## Where the boundary sits This question is about OData's own grammar and evaluation rules. How you would design a sort or filter parameter for a plain REST API, or whether to choose offset or cursor paging there, are separate design questions; OData simply fixes the semantics so that every client and service agree on what one URL means.

  • Why does OData 4.01 require a stable ordering whenever $top or $skip is used without a unique $orderby?
    Positional paging only works if positions do not move between requests. If two customers share a `Name`, they could swap places between the request for positions 11–20 and the one for 21–30, so a customer could appear twice or never. The protocol therefore says the service MUST impose a stable ordering across requests that include `$top` or `$skip`; a client still does well to add a unique key, as in `$orderby=Name,ID`.
  • Why might the OData count returned with $count=true differ from the number of items a client finally pages through?
    The protocol allows it: the count is computed once, and items can be added or removed before the client reaches the last page, or the service may calculate the count inexactly on a large set. A client should treat the count as a display figure, such as a page total, and rely on the absence of a next link, not on the count, to know it has reached the end.

saying these in an interview costs you the question

  • Options are applied left to right in the order they appear in the URL
  • $count=true returns the number of items on the current page
  • $top is applied before $skip when it is written first
  • Without $orderby, rows may come back in any order from page to page
  • $count=true replaces the entities with a bare number
open as a page

You are exposing an OData 4.01 entity set to many client teams; how do you keep arbitrary $filter, $expand and $orderby queries from becoming too expensive?

level: seniorimportance: must knowfreq 19%

basics

~20 s

Support only what you can serve cheaply and fail the rest (unsupported options MUST be rejected, SHOULD with 501), advertise limits with Capabilities annotations such as FilterRestrictions and ExpandRestrictions, and cap every collection with server-driven paging.

open as a page

In OData v4, how does `Customers?$filter=Orders/any(o:o/Amount gt 500)` differ from `Customers?$expand=Orders($filter=Amount gt 500)`?

level: middleimportance: should knowfreq 17%

basics

~20 s

The any filter returns only customers who have at least one order over 500, with no orders inlined. The expand filter returns every customer, each carrying only its orders over 500 — possibly an empty list.

open as a page

In OData 4.01, how do you fetch one city's customers, each with only their five latest orders, and how do nested $expand options behave?

level: middleimportance: should knowfreq 26%

basics

~10 s

Put options in parentheses after the navigation property: Customers?$filter=City eq 'Lyon'&$expand=Orders($orderby=OrderDate desc;$top=5). The semicolon-separated nested options apply to each customer's own orders, not to the whole result.

open as a page

How does OData 4.01 server-driven paging work, and what must a client do with the next link and the maxpagesize preference?

level: middleimportance: should knowfreq 21%

basics

~20 s

An OData 4.01 service may return part of a collection with a next link; the client follows it unchanged until a page has none. Prefer: maxpagesize=N only requests a page size — the service may apply it or choose another.

open as a page

In OData, when would a client use $apply with groupby and aggregate instead of 4.01's $compute, and how does each change the response?

level: seniorimportance: nice to knowfreq 7%

basics

~20 s

$apply transforms the collection itself: groupby and aggregate return one row per group with aliased totals. $compute, new in OData 4.01, adds a calculated property to each existing row without changing how many rows come back.

open as a page