The $filter parameter
The $filter
is the most powerful OData parameter. It has many
operators.
There are built-in logical and mathematical operators that, used in combination with properties
in a result set, can be used to create filter expressions. Some conventions must be followed to
properly use
$filter
expressions. Properties are the fields included in
the API response. Here are some guidelines:- To find out which properties are available to you in a certain API call, look at the Response Class Model.
- Strings must be enclosed in single quotes, properties, operators, and keywords are
not:
$filter=Name eq 'Altoro'
- Sometimes the property you want to use in your expression is nested inside another
property. For example, the Scan JSON object that is returned in the
Get /api/v4/Scans
call has an embedded JSON object in itsLatestExecution
property. To reference this nested field, use a slash. For example to return only scans where the latest scan execution status is Ready use the slash:
This can also be used in other OData parameters, such as:$filter=LatestExecution/Status eq 'Ready'
$orderby=LatestExecution/ScanEndTime desc
- If dates are used in comparisons, the date must be in EDM format (for example,
2018-06-26T09:13:28z
). So in order the get scans created after a certain date, you can compare theCreatedAt
field to a certain date, converting the EDM date string into a DateTime object:$filter=CreatedAt gt 2018-07-31T07:30:00z
Operators
The examples above use operators such as
eq
for "equals" and gt
is "greater than.". Here are some common logical/mathematical operators. For a more complete
list of OData operators see the OData v3 Specification in the Reference.Group | Operator | Meaning |
---|---|---|
Relational | gt |
greater than |
ge |
greater than or equal to | |
lt |
less than | |
le |
less than or equal to | |
isof |
type testing | |
Equality | eq |
equal to |
ne |
not equal to | |
Conditional AND | and |
logical And |
Conditional OR | or |
logical Or |
Lambda operators
Lambda operators enable more advanced expression filtering. There are two lambda operators,any
and all
. They are used on
fields that contain arrays of values that you need to operate on. any
evaluates
to true if at least one result evaluates to true in its expression. All results must evaluate to
true for the operator all
to return true.For example, consider the
/api/v4/Apps
API: each application returned as an array of
ComplianceStatuses, one for each policy associated with the application.- To return all applications that don't have a failed
policy:
$filter=ComplianceStatuses/all(d:d/Compliant eq true)
- To return all applications that have at least one failed
policy:
$filter=ComplianceStatuses/any(d:d/Compliant eq false)
String operators
String operators can be useful because most use cases do not involve strings that are exactly equal. Usually, you need string functions likesubstring
or endswith
. These functions are included in OData.
The following list is taken from the OData specification.String Functions | Example | Meaning |
contains |
contains(Name,'Altoro') |
Returns true if the string contains the substring. |
endswith |
endswith(Name,'Mutual') |
Returns true if the string ends with the substring. |
startswith |
startswith(Name,'Altoro') |
Returns true if the string begins with the substring. |
length |
length(Name) gt 10 |
Returns the length of a string (or array) |
indexof |
indexof(Name,'Altoro') eq 1 |
Returns the index of the beginning of the substring |
substring |
substring(Name,1) eq 'toro Mutual' |
Returns the string beginning at the index. |
tolower |
tolower(Name) eq 'altoro mutual' |
Converts the string to lower case. |
toupper |
toupper(Name) eq 'ALTORO MUTUAL' |
Converts the string to upper case. |
trim
|
trim(Name) eq 'Altoro Mutual' |
Trims trailing and leading white space. |
concat |
concat(Name,' Financial') eq 'Altoro Mutual Financial' |
Concatenates two strings. |
Date specific functions
These functions mostly return specific parts of dates, such as the day of the month from a date object.Date Function | Example |
year |
year(ScanEndTime) eq 2024 |
month |
month(ScanEndTime) eq 12 |
day |
day(ScanEndTime) eq 8 |
hour |
hour(ScanEndTime) eq 1 |
minute |
minute(ScanEndTime) eq 0 |
second |
second(ScanEndTime) eq 0 |
Math functions
Math Functions | Meaning |
round |
Round to the nearest integer |
floor |
Round down to the nearest integer |
ceiling |
Round up to the nearest integer |