Symptom
You wish to know all the available filters when using OData API.
Environment
SAP SuccessFactors HXM Suite
Resolution
There are a few types of filters on OData. They have been grouped on the following topics: Logical, Arithmetic, Grouping, Customized, String Function and Time-Based.
Logical Operators
LOGICAL OPERATOR | DESCRIPTION | EXAMPLE |
---|---|---|
eq | Equal | Finds a User entity whose hr/username is cgrant (hr is the navigation property): https://<API-Server>/odata/v2/User?$filter=hr/username eq 'cgrant'. Finds a User Entity whose username property is cgrant: https://<API-Server>/odata/v2/User?$filter=username eq 'cgrant' . |
ne | Not equal | https://<API-Server>/odata/v2/User?$filter=hr/username ne 'London' |
gt | Greater than | Finds a PickListLabel Entity whose id is greater than 2000:https://<API-Server>/odata/v2/PicklistLabel?$filter=id gt 20000 |
ge | Greater than or equal | https://<API-Server>/odata/v2/PicklistLabel?$filter=id ge 10 |
lt | Less than | https://<API-Server>/odata/v2/PicklistLabel?$filter=id lt 20 |
le | Less than or equal | https://<API-Server>/odata/v2/PicklistLabel?$filter=id le 100 |
and | Logical and | https://<API-Server>/odata/v2/PicklistLabel?$filter=id le 1005 and id gt 1000to find PicklistLabel whose id is within range [1000,1005] |
or | Logical or | https://<API-Server>/odata/v2/PicklistLabel?$filter=id le 3.5 or id gt 200 |
not | Logical negation | https://<API-Server>/odata/v2/User?$filter=not endswith(username,'grant') |
Arithmetic Operators
ARITHMETIC OPERATOR | DESCRIPTION | EXAMPLE |
---|---|---|
add | Addition | https://<API-Server>/odata/v2/PicklistLabel?$filter=id add 5 gt 10 |
sub | Subtraction | https://<API-Server>/odata/v2/PicklistLabel?$filter=id sub 5 gt 10 |
mul | Multiplication | https://<API-Server>/odata/v2/PicklistLabel?$filter=id mul 2 gt 2000 |
div | Division | https://<API-Server>/odata/v2/PicklistLabel?$filter=id div 2 gt 4 |
mod | Modulo | https://<API-Server>/odata/v2/PicklistLabel?$filter=id mod 2 eq 0 |
Grouping Operator
GROUPING OPERATOR | DESCRIPTION | EXAMPLE |
---|---|---|
() | Precedence grouping. | https://<API-Server>/odata/v2/PicklistLabel?$filter=(id sub 5) gt 10 |
Customized Operators
CUSTOMIZED OPERATOR | DESCRIPTION | EXAMPLE |
---|---|---|
in | In clause | Identifies User entities whose userId is equal to one specified by an in clause:https://<API-Server>/odata/v2/User?$filter=userId in 'ctse1','mhuang1','flynch1'&$select=username,userId
Note
Due to the limit of number of expressions (1000) in a list, OData API doesn’t accept an IN clause exceeding 1000 expressions in $filter. |
like | Like clause |
Identifies User entities whose userId property cgrant may be retrieved with a like clause: https://<API-Server>/odata/v2/User?$filter=userId like ’cgrant’ Identifies User entities whose userId property is equivalent to cgrant: $filter=userId eq 'cgrant’ Identifies User entities whose userId property cgrant may be retrieved with a like clause: https://<API-Server>/odata/v2/User?$filter=userId like ’cgrant%’ Identifies User entities whose userId property starts with cgrant: $filter=startswith(userId, 'cgrant’) Identifies User entities whose userId property ends with cgrant: https://<API-Server>/odata/v2/User?$filter=userId like ’%cgrant’ This is equivalent to $filter=endswith(userId, 'cgrant’) Identifies User entities whose userId property contains string cgrant. https://<API-Server>/odata/v2/User?$filter=userId like ’%cgrant%’ Identifies User entities whose userId property contains string cgrant and is case insensitive. https://<API-Server>/odata/v2/User?$filter=tolower(userId) like ’%cgrant%’ https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’ Identifies User entities whose userId is like cgrant and unions with username like cgrant. https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’ or tolower(username) like '%cgrant%’ Identifies User entities whose userId is like cgrant and intersects with username like cgrant. https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’ and tolower(username) like '%cgrant%’ |
String Functions
In addition to operators, a set of functions is also defined for use with the $filter query option. The following table lists the available functions.
STRING FUNCTION | EXAMPLE |
---|---|
bool endswith(string p0, string p1) |
Finds all usernames that end with Futterkiste: https://<API-Server>/odata/v2/User?$filter=endswith(username, 'Futterkiste') |
bool startswith(string p0, string p1) |
Finds all usernames that start with Alfr: https://<API-Server>/odata/v2/User?$filter=startswith(username, 'Alfr') |
bool substringof(string p0, string p1) |
Case-insensitive search for username field: https://<API-Server>/odata/v2/User?$filter=substringof('futter',tolower(username)) eq true |
string tolower(string p0) |
Compare username in lower case to the literal value futterkiste : https://<API-Server>/odata/v2/User?$filter=tolower(username) eq 'futterkiste' |
string toupper(string p0) |
Compare username in upper case to the literal value 'FUTTERKISTE' : https://<API-Server>/odata/v2/User?$filter=toupper(username) eq 'FUTTERKISTE' |
string trim(string p0) |
Trim leading and trailing whitespaces before comparing values: https://<API-Server>/odata/v2/User?$filter=trim(username) eq 'Futterkiste' |
Time-Based
QUERY EXAMPLE | |
---|---|
https://<API-Server>/odata/v2/User?$format=json&$select=lastModified&$filter=lastModified%20ge%20datetime%272003-12-18T17:19:28%27&$top=200 |
See Also
Keywords
filter, odata api, Logical Operators, Arithmetic Operators, Grouping Operator, Customized Operators, String Functions , KBA , LOD-SF-INT-ODATA , OData API Framework , How To