Query Options

An OData query option refers to a collection of query string parameters used to manage the data returned from a resource via the URL. By employing query options, you can effectively regulate the volume of data retrieved from the resource. Essentially, these options enable you to request the service to execute specific transformations, such as filtering, sorting, and more, on its data, thus influencing the final outcome of the results.

Several of the query options ($filter, $expand, $select, $orderby) require knowledge of the entity model in order to correctly construct the url. There is an exposed entity model endpoint here:

curl -H 'Authorization: Bearer your-access-token-goes-here' \
-H 'Accept: application/json' \
'https://query.ampre.ca/odata/$metadata'

note

Remember to URL Encode query string values prior to sending HTTP requests. To aid with readability, the URLs in this document are displayed without URL encoding and wrapped over multiple lines.

Filter

The $filter query option grants clients the ability to apply filtering to a collection of resources identified by a request URL. When using $filter, the provided expression is assessed for each resource in the collection, and only those items for which the expression evaluates to true are included in the response. Any resources where the expression evaluates to false or null, or if they reference properties with restricted permissions, are excluded from the response. This allows for precise control over the data returned based on the specified conditions.

See Common Expression Syntax (Operators, Functions, Literals) section below on leverage operators, functions and literals in the `$filter' expressions.

See Query Option $filter in the OData spec.

Example:

$filter=ContractStatus eq 'Available'
and ModificationTimestamp ge 2023-07-27T04:00:00Z

Expand

The $expand query option allows clients to request related entities to be returned inline with the primary resources, based on navigation properties defined in the entity model.

To determine which related resources can be expanded, examine the service metadata ($metadata) and look for NavigationProperty elements on the entity type.

Within an $expand clause, additional query options may be applied per navigation property, including:

$select – limit returned properties
$filter – restrict related entities
$orderby – control ordering of related entities

Using these options helps control payload size and shape.

There is a rule which limits $top to 100 if $expand is specified.

See Query Option $expand in the OData spec.

Example:

$expand=Media(
  $select=MediaKey,MediaModificationTimestamp,MediaURL,MediaType;
  $filter=MediaType eq 'image/jpeg'
),
Rooms(
  $select=RoomKey,RoomType,ListingKey;
  $orderby=RoomType
)

Expand all navigation properties:

$expand=*

Select

With the $select query option, clients have the capability to request a precise selection of properties for each entity or complex type.

See Query Option $select in the OData spec.

Example:

$select=ListingKey,ContractStatus,ModificationTimestamp

Order By

The $orderby query option enables clients to request resources in a specified order.

See Query Option $orderby in the OData spec.

Example:

$orderby=ModificationTimestamp,ListingKey

Top and Skip

The $top system query option is used to specify the desired number of items from the queried collection to be included in the result. The $skip query option allows excluding a certain number of items from the queried collection in the result. To retrieve a specific page of items, clients can combine $top and $skip accordingly.

See Query Options $top and $skip in the OData spec.

caution

Do not use $top and $skip for replication. $top and $skip are commonly used in Web API queries to limit the amount of data returned by the server. While they can be useful for improving the performance of individual requests, they should not be used for replication purposes. When replicating data, it's important to ensure that all records are transferred and that none are missed. Using $top and $skip can introduce the risk of missing records, especially if records are created or modified while the replication is in progress. Instead, replication should be performed using reliable methods such as timestamp and key-based techniques, which guarantee that all records are transferred in a consistent and accurate manner.

The Problem with `$top` and `$skip`

When utilizing $top and $skip, an issue arises when records are updated while multiple pages are requested sequentially. These updates can result in records being shifted into preceding pages, leading to them being overlooked by the ongoing requests. See the diagram below:

If your implementation requires you to use `$top` and `$skip`

Use both a timestamp and a unique key in the $orderby option. For example: $orderby=ModificationTimestamp,ListingKey
Use a descending $orderby: $orderby=ModificationTimestamp,ListingKey desc. This will ensure that when records are modified during the download, this does not shift the $skip window in a way that causes records to be missed.
Be prepared for individual records to appear in more than one batch, as this will happen when a record is modified while the download is in progress, which causes the $skip window to shift back.
Do not filter by the timestamp field used in $orderby (e.g. ModificationTimestamp), as this will cause records to be missed when modifications happen while the download is in progress.
There is a limit of 100,000 records for the $skip option, therefore $top and $skip can not be used to retrieve more than 100,000 records.

Count

Clients can use the $count query option to obtain a count of the total number records that match the filter included in the request (or simply the total number of records in the resource if no filter is included in the request). The value of the $count query option is a Boolean, which can be set to either true or false.

See Query Option $count in the OData spec.

Examples:

To retrieve only the total number of records in a collection, without any additional results, use $count=true&$top=0:

https://query.ampre.ca/odata/Property?$count=true&$top=0

To retrieve the total number of records in a collection in addition to the results:

https://query.ampre.ca/odata/Property?$count=true&$top=10

Count Response

The count is returned in the @odata.count field of the response JSON.

{
  "@odata.count": 42,
  "value": [
    /* ... */
  ]
}

OData NextLink

The @odata.nextLink annotation enables server-driven paging by providing a URL that clients can use to retrieve the next page of results for a collection query. This allows clients to page through large result sets without relying on large $skip or $top values in a single request.

This implementation addresses client limitations where $skip and $top are capped at 100,000 results, while still allowing access to the full dataset in a predictable and performant way.

When present, @odata.nextLink contains a fully formed request URL that preserves the original query semantics (filters, ordering, expansions, etc.) and advances the result window to the next page.

Implementation Rules

@odata.nextLink is included in the response only when all of the following conditions are met:

The request targets a collection of entities (not a single entity)
The response contains one or more results
A $top value is not specified, or the number of returned results is greater than or equal to the requested $top value

If a $top value is specified and the number of returned entities is less than the requested $top, @odata.nextLink is omitted, indicating there are no additional pages.

Client Usage

Clients should treat the presence of @odata.nextLink as an indication that additional results are available and should follow the provided link verbatim to retrieve the next page. The absence of @odata.nextLink indicates that the current response represents the final page of results.

Example 1: @odata.nextLink not applicable:

Where @odata.nextLink is not present because the result count is smaller than $top.

Request:

/odata/Property?$top=5&$filter=ListingKey eq 'W2635036' or ListingKey eq 'C2675672'&$count=true&$select=ListingKey,ListPrice

Response:

{
    "@odata.context": "https://query.uat.ampre.ca/odata/$metadata#Property",
    "@odata.count": 2,
    "value": [
        {
            "ListingKey": "C2675672",
            "ListPrice": 408800.0
        },
        {
            "ListingKey": "W2635036",
            "ListPrice": 509000.0
        }
    ]
}

Example 2: @odata.nextLink is applicable:

Where @odata.nextLink is present because the result count is greater than $top (and much greater than $skip limits).

Request:

/odata/Property?$top=100&$count=true&$select=ListingKey,ListPrice

Response:

{
    "@odata.context": "https://query.uat.ampre.ca/odata/$metadata#Property",
    "@odata.count": 8972946,
    "value": [
        {
            "ListingKey": "D30869",
            "ListPrice": 990.0
        },
        {
            "ListingKey": "W33797",
            "ListPrice": 284500.0
        },
        {
            "ListingKey": "W18725",
            "ListPrice": 439900.0
        },
        {
            "ListingKey": "C39652",
            "ListPrice": 889000.0
        },
        {
            "ListingKey": "X10482458",
            "ListPrice": 643000.0
        },

        ...

        {
            "ListingKey": "BDAR40762250",
            "ListPrice": 1650000.0
        }
    ],
    "@odata.nextLink": "https://query.uat.ampre.ca/odata/Property?$top=100&$count=true&$select=ListingKey,ListPrice&$skiptoken=%7B%22sort%22:%255B%7B%22ModificationTimestamp%22:%22desc%22%7D,%7B%22ListingKey%22:%22desc%22%7D%255D,%22last_hit_sort_values%22:%255B1762784362000,%22bdar40762250%22%255D%7D"
}

Common Expression Syntax (Operators, Functions, Literals)

The following operators, functions and literals can be used in $filter expressions:

logical operators
arithmetic operators
string and collection functions
date and time functions
arithmetic functions
type functions
geo functions
lambda operators

Logical Operators

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_LogicalOperators]

Supports:

equals
not equals
greather than
greather than or equal
less than
less than or equal
and
or
not
has
in

Arithmetic Operators

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_ArithmeticOperators]

Supports:

addition
subtraction
multiplication
division
modulo

Examples:

DaysOnMarket add 10 eq 100
DaysOnMarket sub 10 eq 80
DaysOnMarket mul 10 eq 900
DaysOnMarket div 2 eq 40
DaysOnMarket mod 10 eq 0

String and Collection Functions

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_StringandCollectionFunctions]

Supports:

concat
contains
endswith
indexof
length
startswith
substring

Examples:

concat(City,', ON') eq 'Oshawa, ON'
concat('Osh', 'awa') eq City
contains(StandardStatus,'Act')
endswith(StandardStatus,'ive')
indexof(StandardStatus,'Active') eq 0
indexof(ListingKey,'C1222') ge 0
length(StandardStatus) eq 6
startswith(StandardStatus,'Act')
substring(StandardStatus,1) eq 'ctive'
substring(StandardStatus,1,2) eq 'ct'

Date and Time Functions

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_DateandTimeFunctions]

Supports:

now
day
hour
minute
month
second
year
date
time
mindatetime
maxdatetime

Examples:

ModificationTimestamp lt now()
day(ModificationTimestamp) eq 1
hour(ModificationTimestamp) eq 1
minute(ModificationTimestamp) eq 1
month(ModificationTimestamp) eq 1
second(ModificationTimestamp) eq 1
year(ModificationTimestamp) eq 2015
date(ModificationTimestamp) eq 2015-03-16
time(ModificationTimestamp) gt 07:59:59
ModificationTimestamp gt mindatetime()
ModificationTimestamp lt maxdatetime()

Arithmetic Functions

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_ArithmeticFunctions]

Supports:

ceiling
floor
round

Examples:

ceiling(BuildingAreaTotal) eq 1000
floor(BuildingAreaTotal) eq 1000
round(BuildingAreaTotal) eq 1

Type Functions

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_TypeFunctions]

Supports:

cast

Examples:

cast(TaxYear, Edm.String) eq '2023'

Geo Functions

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_GeoFunctions]

Supports:

geo.distance
geo.intersects

Examples:

geo.distance(GeoLocation, geography'SRID=4326;POINT(-79.8636286 43.5115285)') lt 5000

geo.distance(GeoLocation, geometry'SRID=4326;POINT(-79.8636286 43.5115285)') lt 5000

geo.intersects(GeoLocation, geography'SRID=4326;POLYGON((-80.10677845998516 43.56076889922026,-79.74554288734276 43.51782942603706,-79.74894160912838 43.39588393391704,-80.10677845998516 43.56076889922026))')

geo.intersects(GeoLocation, geometry'SRID=4326;POLYGON((-80.10677845998516 43.56076889922026,-79.74554288734276 43.51782942603706,-79.74894160912838 43.39588393391704,-80.10677845998516 43.56076889922026))')

Lambda Operators

[https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_LambdaOperators]

Supports:

Examples:

ConstructionMaterials/all(t:t eq 'Brick')

ConstructionMaterials/any(t:t eq 'Brick')

ConstructionMaterials/any(t:t eq 'Brick') and ConstructionMaterials/any(t:t eq 'Other')

ConstructionMaterials/all(t:t ne 'Brick')

ConstructionMaterials/all(t:(startswith(t, 'Bri')))

Query Options

Filter​

Example:​

Expand​

Example:​

Select​

Example:​

Order By​

Example:​

Top and Skip​

The Problem with $top and $skip​

If your implementation requires you to use $top and $skip​

Count​

Examples:​

Count Response​

OData NextLink​

Implementation Rules​

Client Usage​

Example 1: @odata.nextLink not applicable:​

Example 2: @odata.nextLink is applicable:​

Common Expression Syntax (Operators, Functions, Literals)​

Logical Operators​

Arithmetic Operators​

String and Collection Functions​

Date and Time Functions​

Arithmetic Functions​

Type Functions​

Geo Functions​

Lambda Operators​

Filter

Example:

Expand

Example:

Select

Example:

Order By

Example:

Top and Skip

The Problem with `$top` and `$skip`

If your implementation requires you to use `$top` and `$skip`

Count

Examples:

Count Response

OData NextLink

Implementation Rules

Client Usage

Example 1: @odata.nextLink not applicable:

Example 2: @odata.nextLink is applicable:

Common Expression Syntax (Operators, Functions, Literals)

Logical Operators

Arithmetic Operators

String and Collection Functions

Date and Time Functions

Arithmetic Functions

Type Functions

Geo Functions

Lambda Operators