Query Options
Version 22.0.8486
Version 22.0.8486
Query Options
You can use an HTTP GET request to retrieve all records, filter records, sort results, and restrict the data returned from each entity. The path of the URL specifies the set of entities to retrieve. For example, to retrieve all records from the Car entity, use the following URL:
https://myconnectserver/odata.rsc/Cars
Individual Record
To retrieve a single record, make a request to the URL for that entity. To construct the URL, use the desired entity’s primary key. For example:
https://myconnectserver/odata.rsc/Cars('1000')
Some entities may have multiple primary keys, which are indexed as shown in the following example:
https://myconnectserver/odata.rsc/Cars(Id='1000', Date='2016-07-01')
Filtering
Client applications can retrieve multiple entities based on filters provided in the request, using the $filter URL parameter. For example, a filter to retrieve all records where Make matches ‘Honda’ would look like the following:
https://myconnectserver/odata.rsc/Cars?$filter=Make eq 'Honda'
Connect OData API 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 the boolean operators ‘and’ and ‘or’ to combine multiple filters:
https://myconnectserver/odata.rsc/Cars?$filter=Make eq 'Honda' and Date lt '2016-07-01'
The functions startswith(), endswith(), toupper(), tolower(), and contains() can be used with the $filter query option. For example, the following request returns records with properties that contain the specified substring:
https://myconnectserver/odata.rsc/Cars?$filter=contains(Make,'Honda')
Selecting Properties
To retrieve a subset of properties, use $select, as shown in the following example:
https://myconnectserver/odata.rsc/Cars?$select=Id,Model
This returns the properties Id and Model for all records that match the filter in the request.
You can also retrieve an individual property value for a single record, as shown in the following example:
https://myconnectserver/odata.rsc/Cars('1000')/Model/$value
Sorting
You can use $orderby to sort records, as shown in the following example:
https://myconnectserver/odata.rsc/Cars?$orderby=Model asc, Color desc
This returns the records sorted by Model (ascending) and then by Color (descending).
Pagination
The Connect supports different ways to page through data, when requests are too large to send in a single response.
Server-Side
The Connect OData API supports server-side paging, which can be enabled in the Settings tab of the OData page 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 records and an @odata.nextLink attribute containing the URL for the next page of records:
{
"@odata.context": "https://myconnectserver/odata.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":"https://myconnectserver/odata.rsc/Cars?$skiptoken=0f87696b-aa28-4a70-b13d-c86af8338c80"
}
Many client applications can handle Server-side pagination seamlessly. If you are implementing your own OData client application, or calling the OData API manually, you can retrieve the subsequent pages of data by passing the $skiptoken a URL parameter, or using the URL from the response attribute @odata.nextLink directly:
https://myconnectserver/odata.rsc/Cars?$skiptoken=0f87696b-aa28-4a70-b13d-c86af8338c80
Client-Side
The CData Connect API also supports client-side paging using $top and $skip. Note that Client-side paging is much less efficient than server side paging, and should only be used if the client application does not support server-side paging.
You can use $top=N to include only the first N records in the result. For example, use the following request to show the top ten Cars records:
https://myconnectserver/odata.rsc/Cars?$top=10
You can use $skip=N to exclude the first N records 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 records in two pages:
https://myconnectserver/odata.rsc/Cars?$top=10
https://myconnectserver/odata.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:
https://myconnectserver/odata.rsc/Cars?$top=3&$skip=4&$count=true
This query returns a response like the following:
{
"@odata.context": "https://myconnectserver/odata.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"}
]
}
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
Using the $count URL parameter, you can retrieve the number of records in an entity, or the number of records matching a particular filter, as shown in the following example:
https://myconnectserver/odata.rsc/Cars?$count=true&$filter=Make eq 'Honda'
The response is a raw count of records matching the filter in the request.
Other OData Options
Additional OData functionalities are described in their own articles: