Skip to main content

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.

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 Query Option $filter in the OData spec.

Example:​

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

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:

Problem with Top and Skip

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": [
    /* ... */
  ]
}