Reference
Filtering
How Filtering Works
API endpoints that allow you to list typically provide two ways of filtering the results.
Query
You can use the query parameter to filter the results by a search term. The search term is matched against the name of the resource. For example, to list all the projects that contain the word “test” in their name, you can use the following query:Filter
Filters allow for a more fine-grained filtering of the results. You can use the filter parameter to filter the results by a specific field. For example, to list all the projects that have the name “test”, you can use the following filter:Fields
Field names should be directly given without any extra literals. Dots indicate nested fields. For example:category.updatedAt
Inputs
Numbers should be directly given. Booleans should also directly be given, valid values aretrue and false. Others such as strings, enums, dates, should be quoted. For example: status : 'active'
Operators
| Literal | Description | Example |
|---|---|---|
| and | and’s two expressions | status : ‘active’ and createdAt > ‘1-1-2000’ |
| or | or’s two expressions | value ~ ‘%hello%’ or name ~ ‘%world%‘ |
| not | not’s an expression | not (id > 100 or category.order is null) |
You may prioritize operators using parentheses, for example: x and (y or z)
Comparators
| Literal | Description | Example |
|---|---|---|
| ~ | checks if the left (string) expression is similar to the right (string) expression | catalog.name ~ ‘electronic’ |
| ~~ | similar to the previous operator but case insensitive | catalog.name ~~ ‘ElEcTroNic*‘ |
| : | checks if the left expression is equal to the right expression | id : 5 |
| ! | checks if the left expression is not equal to the right expression | username ! ‘torshid’ |
| > | checks if the left expression is greater than the right expression | distance > 100 |
| >: | checks if the left expression is greater or equal to the right expression | distance >: 100 |
| < | checks if the left expression is smaller than the right expression | distance < 100 |
| <: | checks if the left expression is smaller or equal to the right expression | distance <: 100 |
| isNull | checks if an expression is null | status isNull |
| isNotNull | checks if an expression is not null | status isNotNull |
| is empty | checks if the (collection) expression is empty | children is empty |
| is not empty | checks if the (collection) expression is not empty | children is not empty |
| in | checks if an expression is present in the right expressions | status in [‘initialized’, ‘active’] |
| not in | checks if an expression is not present in the right expressions | status not in [‘failed’, ‘closed’] |
Note that the * character can also be used instead of % when using the ~ comparator. By default, this comparator
is case insensitive, the behavior can be changed with FilterParameters.CASE_SENSITIVE_LIKE_OPERATOR.
Functions
A function is characterized by its name (case insensitive) followed by parentheses. For example:currentTime(). Some
functions might also take arguments, arguments are separated with commas. For example: min(ratings) > 3
| Name | Description | Example |
|---|---|---|
| absolute | returns the absolute | absolute(x) |
| average | returns the average | average(ratings) |
| min | returns the minimum | min(ratings) |
| max | returns the maximum | max(ratings) |
| sum | returns the sum | sum(a, b), sum(scores) |
| diff | returns the difference | diff(a, b) |
| prod | returns the product | prod(a, b) |
| quot | returns the quotient | quot(a, b) |
| mod | returns the modulus | mod(a, b) |
| sqrt | returns the square root | sqrt(a) |
| currentDate | returns the current date | currentDate() |
| currentTime | returns the current time | currentTime() |
| currentTimestamp | returns the current timestamp | currentTimestamp() |
| size | returns the collection’s size | size(accidents) |
| length | returns the string’s length | length(name) |
| trim | returns the trimmed string | trim(name) |
| upper | returns the uppercased string | upper(name) |
| lower | returns the lowercased string | lower(name) |
| concat | returns the concatenation of given strings | concat(firstName, ’ ’, lastName) |
Subqueries
| Name | Description | Example | Explanation |
|---|---|---|---|
| exists | returns the existence of a subquery result | exists(employees.age > 60) | returns true if at least one employee’s age is greater than 60 |