Getting and Filtering Data
The ChannelAdvisor REST API supports the OData 4.0 specification, which is an OASIS standard that supports filtering, sorting, paging, selecting and expanding. The select and expand capabilities allow developers to specify the exact details they want to retrieve as parameters in the request. This both reduces load on ChannelAdvisor servers and decreases JSON payload sizes for more efficient network usage. Use of these features improves performance for downstream systems and ultimately leads to higher customer satisfaction.
Filtering
Requests can be filtered by using the $filter keyword. Filters utilizing a text string value require single quotes around the value, otherwise no single quotes are required.
The following logical operations are supported:
| Operation | Description | Example |
|---|---|---|
| and | And | Field1 eq 'abc' and Field2 eq 'def' |
| or | Or | Field1 eq 'abc' or Field1 eq 'def' |
| eq | Equals | BuyerEmailAddress eq 'first.last@example.com' |
| ne | Not Equals | BuyerEmailAddress ne 'first.last@example.com' |
| gt | Greater Than | CreatedDateUtc gt 2016-03-02 |
| ge | Greater Than Or Equal | CreatedDateUtc ge 2016-03-02 |
| lt | Less Than | Quantity lt 1500 |
| le | Less Than Or Equal | Quantity le 1500 |
Additionally, there are several functions that can be used to help filter specific data types.
| Function | Description | Data Type | Example |
|---|---|---|---|
| Year | Returns the year component (without the fractional part) of a DateTimeOffset | Integer | year(BirthDate) eq 1971 |
| Month | Returns the month component (without the fractional part) of a DateTimeOffset | Integer | month(BirthDate) eq 5 |
| Day | Returns the day component (without the fractional part) of a DateTimeOffset | Integer | day(BirthDate) eq 8 |
| Hour | Returns the hour component (without the fractional part) of a DateTimeOffset | Integer | hour(BirthDate) eq 4 |
| Minute | Returns the minute component (without the fractional part) of a DateTimeOffset | Integer | minute(BirthDate) eq 40 |
| Second | Returns the second component (without the fractional part) of a DateTimeOffset | Integer | second(BirthDate) eq 40 |
| Floor | Rounds the input numeric parameter down to the nearest numeric value with no decimal component | Decimal | floor(Freight) eq 32 |
| Ceiling | Rounds the input numeric parameter up to the nearest numeric value with no decimal component | Decimal | ceiling(Freight) eq 32 |
Notes
- String functions such as 'SubstringOf' and 'StartsWith' are currently not supported.
- When using any of the filters to reference a specific date, but no specific time in a filter, the API assumes the time is 12:00.00am UTC.
- When sending a request to retrieve Orders with $filter=CreatedDateUtc eq 2015-11-15, the only result would be an order created 2015-11-15 at 12:00.00am UTC.
- See some examples below in the "Filtering on properties" for filtering between date ranges or after a specific time.
- To capture all of an item with an Order filter created up to the moment the request is made, try $filter=CreatedDateUtc ge 2015-11-15 (assumes today is November 15, 2015)
- Case-Sensitivity in URIs:
- Every property definition before the "?" in a URI is case-sensitive (e.g. attribute name string values)
- Everything after the "?" in a URI is not case-sensitive (e.g. $filter=property definitions, operators; $select, $orderby, etc)
Selecting Properties
| Query | Example |
|---|---|
| Selecting a subset of properties | https://api.channeladvisor.com/v1/Orders?$select=ID, ShippingStatus, CreatedOnDateUtc |
| Filtering on properties | https://api.channeladvisor.com/v1/Orders?$filter=BuyerEmailAddress eq 'first.last@example.com' |
| Sorting by a property | https://api.channeladvisor.com/v1/Orders?$orderby=CreatedOnDateUtc desc |
| Returning the total count of an endpoint | https://api.channeladvisor.com/v1/Orders/$count |
| Returning the total count of a filtered endpoint | https://api.channeladvisor.com/v1/Orders?$filter=ProfileID eq 12345678 and CreatedDateUtc gt 2016-05-01&$count=true |
| Paging through results | Page 1: https://api.channeladvisor.com/v1/Orders |
| Expanding child collections | https://api.channeladvisor.com/v1/Orders?$expand=Items |
| Expanding multiple child collections | https://api.channeladvisor.com/v1/Orders?$expand=Items,Fulfillments |
| Expanding 2nd level child collections | https://api.channeladvisor.com/v1/Orders?$expand=Fulfillments($expand=Items) |
| Expanding 2nd level child collections for multiple child collections | https://api.channeladvisor.com/v1/Orders?$expand=Items($expand=Adjustments),Fulfillments($expand=Items) |
| Selecting properties on child collections | https://api.channeladvisor.com/v1/Orders?$expand=Items($select=Sku, Quantity) |
Filtering based on child collection property value | https://api.channeladvisor.com/v1/Orders?$filter=Adjustments/Any(c: c/Reason eq '2') |
| Filtering based on multiple property values (one at endpoint level and one at child collection level) | https://api.channeladvisor.com/v1/Products?$filter=Attributes/Any (c: c/Value eq '13M') and Brand eq 'Skechers' and Attributes/Any (c: c/Name eq 'Size') &$expand=Attributes |
| Retrieve the first 50 products with ID and Sku only | https://api.channeladvisor.com/v1/Products?$select=ID,Sku&$top=50 |
| Retreive the second 50 products with ID and Sku only Note: $skip must appear before $top for this to work | https://api.channeladvisor.com/v1/Products?$select=ID,Sku&$skip=50&$top=50 |
DateTimeOffset
Read about "Lexical Mapping" on this W3 page for more information on formatting DateTimeOffset.
Proper Format:
Any of the below formats are accurate within the API and will be interpreted correctly within POST/PATCH/PUT requests.
GET requests will allow the below, but do not require the time to execute properly.
- 2016-10-31T14:08:36.8033257Z
- 2016-08-19T10:44:15Z
- 2016-10-31T14:08:36.8033257-04:00
- 2016-10-31T14:08:36.8033257+12:00
- 2016-08-19T10:44:15+01:00
Paging Through Results
Each page of results will have a link to the next page embedded in the @odata.nextLink property. The last page will not contain a value for @odata.nextLink, indicating the end of the result set.
It is highly recommended to use the @odata.NextLink URI for paging instead of manually setting a $skip value because maximum page sizes may vary.
Page Sizes
GET /v1/Orders and /v1/Products: default response page size is 100 when no filters are defined.
Other endpoints, including sub-collections: default response page size is 100 with some exceptions and are designed this way to optimize performance and efficiency.
Important Note: If filters are included in the query, the page size may be reduced to 20 orders or items per page.