Skip to content

Simple filters

Most list routes of the API support filtering the result list using query parameters.

Operators

You can access any field available in the data model using the following operators:

field's data type query key suffix meaning example
datetime (none) for datetime fields, such as inserted_at, writing its name as the key will filter it using truncate rules, see below inserted_at=2020-10-03T13:50 everything within 13:50:00 and 13:50:59.999 on the 3rd of October, 2020
datetime remove _at, add _after will only include entries after the given datetime measured_after=2020-10 everything measured during or after October 2020
datetime remove _at, add _before will only include entries before the given datetime updated_before=2020-01-01 everything updated before first of January 2020
other datatypes _is the given value exactly equals the field name_is=Peter only results where the name is exactly "Peter" (case sensitive)
other datetypes _is_not the given value does not equal the field packet_type_is_not=up only packets with a type other than up
other datetypes _after the given value is larger or equals the field name_after=Z only results where the name is or comes after "Z" when ordering alphabetically
other datetypes _before the given value is smaller or equals the field name_before=A only results where the name is or comes before "A" when ordering alphabetically
string _like the given pattern matches the string field name_like=A% only results where the name begins with the letter A
string _ilike the given pattern matches the string field, but lower/uppercase is ignored name_ilike=A% only results where the name begins with the letter A or a
string _not_like the given pattern does not match the string field name_not_like=A% only results where the name does not begin with the letter A
string _not_ilike the given pattern does not match the string field (case insensitive) name_not_ilike=A% only results where the name does not begin with the letters A or a

Truncate rules

Simple filters can detect how you want to filter for a date when you supply it with the _at operator. Depending on how specific the supplied date is, the filter will include results that are in the resulting range.

For example, if you only supply 2020-10, the filter will return results from the whole month, while giving something like 2020-03-14T12:30:30Z will only return results during that second.

  • 2020, giving only a year, will return all results from 2020
  • 2020-01, additionally giving the month, will filter it down to only January 2020
  • 2020-01-10, with all 3 parts of the date included, return only results from the 10th of January, 2020
  • 2020-01-10T12Z, when the hour part is added, everything between 12:00:00 and 12:59:59.999 is returned
  • 2020-01-10T12:30Z with only the second missing, the filters range narrows to 12:30:00 and 12:30:59.999.
  • 2020-01-10T12:30:02Z finally, the datetime is complete, and the filter will only return what has happened during the 2nd second of the 3th minute

Timezone handling

If no timezone is supplied in the datetime string, "Zulu"/UTC time is assumed.

You can supply a timezone offset like +02:00 (for CEST) in every datetime string that includes at least the hour part of the time.

Hint: when giving a positive timezone offset, the + sign has to be URL encoded, because a + in a query parameter will be interpreted as a space character, leading to a parse error. + in URL encoding is %2B. If your API client automatically encodes query params, you don't have to do it manually.

_(not)_(i)like operators

All of the like operators use the LIKE pattern matching syntax of PostgreSQL:

The _like [operator] returns true if the string matches the supplied pattern. (As expected, the _not_like [operator] returns false if like returns true, and vice versa. [...])

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case _like acts like the _is operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

Some examples:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

Hint: when using the percent sign (%) in a like pattern, it has to be URL encoded, if your API client does not do this automatically. % in URL encoding is %25.