Filtering

Modified on Sun, 30 Oct 2022 at 10:19 AM

Our filter allows the use of a range of filter parameters to allow you to filter results, it applies across our REST API and also if available in the document and data views in the UI.

These are added a "filter" in the query parameters if you are using the API.

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 are true 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%'
:	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
is null	checks if an expression is null	status is null
is not null	checks if an expression is not null	status is not null
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')

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 seperated 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 time stamp	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