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 its LatestExecution 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:
    $filter=LatestExecution/Status eq 'Ready'
    This can also be used in other OData parameters, such as:
    $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 the CreatedAt 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.
Table 1. Operators
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 like substring or endswith. These functions are included in OData. The following list is taken from the OData specification.
Table 2. String operators
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.
Table 3. Date functions
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

Table 4. 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