How to use the $filter system query option when using API's
$Filter
Orchestrator API allows to filter the results returned based on the key-value pairs included in the response body. Filter the results by adding ?$filter=xxx to the end of GET endpoints with the filter condition xxx is crafted using the following operators and functions in the table below. When using operators and functions, pay attention to the data type of the values in the key-value pairs. Not all operators and functions works with every value. For example, filtering for results where the name is greater than a number is not going to give the expected results. Below are some examples.
FolderID
Many API calls require folderID to be included in the header of the request. To find the folderID of a given folder, use the following filter call
- GET /odata/Folders?$filter=FullyQualifiedName eq 'foldernamehere'
Nested Key-Value Pairs
If the key or value required to filter on is nested, use the / to specify the hierarchy
- GET /odata/Jobs?$filter=(Robot/Id eq 123L)
- GET /odata/Folders?$filter=fullyqualifiedname eq parentfolder/childfolder
Null
There is no isnull operator, so instead to determine if a value is null use “eq null”
- GET /odata/Jobs?filter=(EndTime eq null)
Combining Filters
Filters can be combined. The below call searches for jobs with type process created after April 4, 2024 noon
- GET /odata/Jobs?$filter=((CreationTime ge 2024-04-04T12:00:00.000Z) and (ProcessType eq 'Process'))
Operators
| Operator | Description | Example |
| Comparison Operators | ||
| eq | Equal | Address/City eq 'Redmond' |
| ne | Not equal | Address/City ne 'London' |
| gt | Greater than | Price gt 20 |
| ge | Greater than or equal | Price ge 10 |
| lt | Less than | Price lt 20 |
| le | Less than or equal | Price le 100 |
| has | Has flags | Style has Sales.Color'Yellow' |
| Logical Operators | ||
| and | Logical and | Price le 200 and Price gt 3.5 |
| or | Logical or | Price le 3.5 or Price gt 200 |
| not | Logical negation | not endswith(Description,'milk') |
| Arithmetic Operators | ||
| add | Addition | Price add 5 gt 10 |
| sub | Subtraction | Price sub 5 gt 10 |
| mul | Multiplication | Price mul 2 gt 2000 |
| div | Division | Price div 2 gt 4 |
| mod | Modulo | Price mod 2 eq 0 |
| Grouping Operators | ||
| ( ) | Precedence grouping | (Price sub 5) gt 10 |
Functions
| Function | Example |
| String Functions | |
| contains | contains(CompanyName,'freds') |
| endswith | endswith(CompanyName,'Futterkiste') |
| startswith | startswith(CompanyName,'Alfr') |
| length | length(CompanyName) eq 19 |
| indexof | indexof(CompanyName,'lfreds') eq 1 |
| substring | substring(CompanyName,1) eq 'lfreds Futterkiste' |
| tolower | tolower(CompanyName) eq 'alfreds futterkiste' |
| toupper | toupper(CompanyName) eq 'ALFREDS FUTTERKISTE' |
| trim | trim(CompanyName) eq 'Alfreds Futterkiste' |
| concat | concat(concat(City,', '), Country) eq 'Berlin, Germany' |
| Date Functions | |
| year | year(BirthDate) eq 0 |
| month | month(BirthDate) eq 12 |
| day | day(StartTime) eq 8 |
| hour | hour(StartTime) eq 1 |
| minute | minute(StartTime) eq 0 |
| second | second(StartTime) eq 0 |
| fractionalseconds | second(StartTime) eq 0 |
| date | date(StartTime) ne date(EndTime) |
| time | time(StartTime) le StartOfDay |
| totaloffsetminutes | totaloffsetminutes(StartTime) eq 60 |
| now | StartTime ge now() |
| mindatetime | StartTime eq mindatetime() |
| maxdatetime | EndTime eq maxdatetime() |
| Math Functions | |
| round | round(Freight) eq 32 |
| floor | floor(Freight) eq 32 |
| ceiling | ceiling(Freight) eq 33 |