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

LiteralDescriptionExample
andand's two expressionsstatus : 'active' and createdAt > '1-1-2000'
oror's two expressionsvalue ~ '%hello%' or name ~ '%world%'
notnot's an expressionnot (id > 100 or category.order is null)

You may prioritize operators using parentheses, for example: x and (y or z)

Comparators

LiteralDescriptionExample
~checks if the left (string) expression is similar to the right (string) expressioncatalog.name ~ 'electronic%'
:checks if the left expression is equal to the right expressionid : 5
!checks if the left expression is not equal to the right expressionusername ! 'torshid'
>checks if the left expression is greater than the right expressiondistance > 100
>:checks if the left expression is greater or equal to the right expressiondistance >: 100
<checks if the left expression is smaller than the right expressiondistance < 100
<:checks if the left expression is smaller or equal to the right expressiondistance <: 100
is nullchecks if an expression is nullstatus is null
is not nullchecks if an expression is not nullstatus is not null
is emptychecks if the (collection) expression is emptychildren is empty
is not emptychecks if the (collection) expression is not emptychildren is not empty
inchecks if an expression is present in the right expressionsstatus 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

NameDescriptionExample
absolutereturns the absoluteabsolute(x)
averagereturns the averageaverage(ratings)
minreturns the minimummin(ratings)
maxreturns the maximummax(ratings)
sumreturns the sumsum(a, b), sum(scores)
diffreturns the differencediff(a, b)
prodreturns the productprod(a, b)
quotreturns the quotientquot(a, b)
modreturns the modulusmod(a, b)
sqrtreturns the square rootsqrt(a)
currentDatereturns the current datecurrentDate()
currentTimereturns the current timecurrentTime()
currentTimestampreturns the current time stampcurrentTimestamp()
sizereturns the collection's sizesize(accidents)
lengthreturns the string's lengthlength(name)
trimreturns the trimmed stringtrim(name)
upperreturns the uppercased stringupper(name)
lowerreturns the lowercased stringlower(name)
concatreturns the concatenation of given stringsconcat(firstName, ' ', lastName)

Subqueries

NameDescriptionExampleExplanation
existsreturns the existence of a subquery resultexists(employees.age > 60)returns true if at least one employee's age is greater than 60

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article