Filtering Resources

Version 23.0.8844


Filtering Resources

Version 23.0.8844


You can use an HTTP GET request to retrieve all resources, filter resources, sort resources, and restrict the data returned from each resource. The path of the URL specifies the set of resources to retrieve. For example, to retrieve all Cars resources, use the following URL:

http://MyServer:MyPort/api.rsc/Cars

Single Resource

To retrieve a single resource, make a request to the URL for that resource. To construct the URL, use the desired resource’s primary key. For example:

http://MyServer:MyPort/api.rsc/Cars('1000')

Some resources may have multiple primary keys, which are indexed as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars(Id='1000', Date='2016-07-01')

Filtering

Client applications can retrieve multiple resources based on filters provided in the request. For example, a filter to retrieve all resources where Make matches ‘Honda’ would look like the following:

http://MyServer:MyPort/api.rsc/Cars?$filter=Make eq 'Honda'

The API Server supports the following logic operators for comparison:

Eq equal
Ne Not equal
Gt Greater than
Ge Greater than or equal
Lt Less than
Le Less than or equal
Not Negation

You can also use ‘and’ and ‘or’ to combine multiple filters:

http://MyServer:MyPort/api.rsc/Cars?$filter=Make eq 'Honda' and Date lt '2016-07-01'

The startswith, endswith, toupper, tolower, and contains functions can be used with the $filter query option. For example, the following request returns resources with properties that contain the specified substring:

http://MyServer:MyPort/api.rsc/Cars?$filter=contains(Make,'Honda')

Selecting Properties

To retrieve a subset of properties, use $select, as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars?$select=Id,Model

This returns the properties Id and Model for all resources that match the filter in the request. You can also retrieve an individual property value for a single resource, as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars('1000')/Model/$value

Sorting

You can use $orderby to sort resources, as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars?$orderby=Model asc, Color desc

This returns the resources sorted by Model (ascending) and then by Color (descending).

Pagination

Server-Side

The API Server supports server-side paging, which can be enabled in the Settings > Server section of the application. When the page-size is greater than 0 and a request returns results larger than the page size, the URL for the next page of results is included in the @odata.nextlink attribute of the response. The last page of results does not include this attribute. This URL includes a paging token which remains valid for the next two minutes. For example, the following response has three resources and an @odata.nextLink attribute containing the URL for the next page of records:

{
  "@odata.context": "http://MyServer:MyPort/api.rsc/$metadata#Cars",
  "value": [
    { "Id": "Id_1", "Color": "Color_1", "Model": "Model_1"},
    { "Id": "Id_2", "Color": "Color_2", "Model": "Model_2"},
    { "Id": "Id_3", "Color": "Color_3", "Model": "Model_3"}
  ],
  "@odata.nextLink":"http://MyServer:MyPort/api.rsc/Cars?$skiptoken=0f87696b-aa28-4a70-b13d-c86af8338c80"
}

Client-Side

The CData API Server also supports client-side paging using $top, $skip, and $count.

You can use $top=n to include only the first n resources in the result. For example, use the following request to show the top ten Cars resources:

http://MyServer:MyPort/api.rsc/Cars?$top=10

You can use $skip=n to exclude the first n resources from the result. You can use $top with $skip to implement client-side paging. $skip is always applied before $top, regardless of their order in the query. For example, the following two queries retrieve the first 20 resources in two pages:

http://MyServer:MyPort/api.rsc/Cars?$top=10
http://MyServer:MyPort/api.rsc/Cars?$top=10&$skip=10

You can also set the parameter $count to true to return the total number of records in the results. If using OData version 2.0 or 3.0, you can set $inlinecount to allpages instead. For example, consider the following query:

http://MyServer:MyPort/api.rsc/Cars?$top=3&$skip=4&$count=true

This query may return a response like the following:

{
  "@odata.context": "http://MyServer:MyPort/api.rsc/$metadata#Cars",
  "@odata.count": 402,
  "value": [
    { "Id": "Id_1", "Color": "Color_1", "Model": "Model_1"},
    { "Id": "Id_2", "Color": "Color_2", "Model": "Model_2"},
    { "Id": "Id_3", "Color": "Color_3", "Model": "Model_3"}
  ],
  "@odata.nextLink":"http://MyServer:MyPort/api.rsc/Account?$skip=7"
}

As you can see, the total count that matches the filter is returned in the response along with the single page of results.

Count-Only

You can retrieve the count only for resources matching a particular filter with the query, as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars?$count=true&$filter=Make eq 'Honda'

The response is a raw count of resources matching the filter in the request.