Direkt zum Hauptinhalt

Filter

The filter parameter in the
loadrecords
and
loadobjecttypes
functions is a complex JSON object representing the query criteria.
If the filter is omitted, no filter is applied.
A filter object can have different characteristics and also contain other filter objects. Multiple nested filters can therefore be defined.

Filter classes

Comparer

This filter performs one or more comparison operations on a field.
The properties of this filter have the name of a field and as value another object that describes the comparison operation and the comparison values:
{field name: {operator: value}}
Some comparison operations expect 2 or more comparison values. These are put into an array:
{field name: {operator: [value1, value2, ...]}}
Multiple comparison operations can also be specified for a single field by placing them in an array: (The comparison operations are ANDed, so they must all apply)
{field name: [{operator1: value1},{operator2: value2},...]}
Example:
{$system_id: {$equals, "123456"}}  ≙ select * from tab where system_id='123456'

comparison operations

OperatorValue (data type)DescriptionExamplecorresponds to this SQL query
$isEmptybooltrue only; field has empty value; identical to $isNull{system_id: {$isEmpty: true}}system_id IS NULL
$notEmptybooltrue only; field has no empty value{system_id: {$notEmpty: true}}system_id<>''
$isNullbooltrue only; field value is NULL{system_id: {$isNull: true}}system_id IS NULL
$notNullbooltrue only; field value is not NULL{system_id: {$notNull: true}}system_id IS NOT NULL
$equalsanyfield value equals comparison value{system_id: {$equals: "7"}}
shorthand: {system_id: "7"}		system_id='7'
$notEqualsanyfield value not equal to comparison value{system_id: {$notEquals: "7"}}system_id<>'7'
$endswithstringfield value ends with comparison value{system_id: {$endswith: "7"}}system_id LIKE '%7'
$startswithstringfield value starts with comparison value{system_id: {$startswith: "7"}}system_id LIKE '7%'
$containsstringfiel value contains comparison value{system_id: {$contains: "7"}}system_id LIKE '%7%'
$likestringfield value matches the pattern{system_id: {$like: "%7_7%"}}system_id LIKE '%7_7%'
$inany[]field value equals one of the comparison values{system_id: {$in: ["7","8","9"]}}system_id IN ('7','8','9')
$notInany[]field value not equal to any of the comparison values{system_id: {$notIn: ["7","8","9"]}}system_id NOT IN ('7','8','9')
$betweenany[2]field value lies between the two comparison values{system_id: {$between: ["7","9"]}}system_id BETWEEN '7' AND '9'
$notBetweenany[2]field value not lies between the two comparison values{system_id: {$notBetween: ["7","9"]}}system_id NOT BETWEEN '7' AND '9'
$gtanyfield value is greater than comparison value{system_id: {$gt: "7"}}system_id > '7'
$gteanyfield value is greater than or equal to comparison value{system_id: {$gte: "7"}}system_id >= '7'
$ltanyfield value is less than comparison value{system_id: {$lt: "7"}}system_id < '7'
$lteanyfield value is less than or equal to comparison value{system_id: {$lte: "7"}}system_id <= '7'
$equals_fieldstringfield value is equal to specified other field{system_last_change_at: {$equals_field: "system_created_at"}}system_last_change_at = system_created_at
$notEquals_fieldstringfield value not equal to specified other field{system_last_change_at: {$notEquals_field: "system_created_at"}}system_last_change_at <> system_created_at
$gt_fieldstringfield value is greater than specified other field{system_last_change_at: {$gt_field: "system_created_at"}}system_last_change_at > system_created_at
$gte_fieldstringfield value is greater than or equal to specified other field{system_last_change_at: {$gte_field: "system_created_at"}}system_last_change_at >= system_created_at
$lt_fieldstringfield value is less than specified other field{system_last_change_at: {$lt_field: "system_created_at"}}system_last_change_at < system_created_at
$lte_fieldstringfield value is less than or equal to specified other field{system_last_change_at: {$lte_field: "system_created_at"}}system_last_change_at <= system_created_at
$between_fieldstring[2]field value lies between the values of two other specified fields{system_last_change_at: {$between_field: ["system_created_at","system_delete_at"]}}system_last_change_at BETWEEN system_created_at AND system_delete_at
$notBetween_fieldstring[2]field value not lies between the values of two other specified fields{system_last_change_at: {$notBetween_field: ["system_created_at","system_delete_at"]}}system_last_change_at NOT BETWEEN system_created_at AND system_delete_at
$dateintDate/Time only: day of month equals comparison value{system_created_at: {$date: 1}}DAY(system_created_at) = 1
$dayintDate/Time only: weekday equals comparison value{system_created_at: {$day: 1}}DATEPART(dw, system_created_at) = 1
$yearintDate/Time only: year equals comparison value{system_created_at: {$year: 2000}}YEAR(system_created_at)=2000
$monthintDate/Time only: month equals comparison value{system_created_at: {$month: 12}}MONTH(system_created_at)=12
$minutesintDate/Time only: minute equals comparison value{system_created_at: {$minutes: 59}}DATEPART(mi, system_created_at) = 59
$hoursintDate/Time only: hour equals comparison value{system_created_at: {$hours: 18}}DATEPART(hh, system_created_at) = 18

ALL

Default filter. This filter loads all records without restrictions.
Example:
{$all: true} ≙ select * from tab where 1=1
only true is allowed

EMPTY

This filter creates an empty query. Usually not required.
Example:
{$empty: true} ≙ select * from tab where 1=0
only true is allowed

NOT

This filter negates the included filter
Example:
{$not: {system_id: "7"}} ≙ select * from tab where NOT (sytem_id='7')

AND

This filter connects the contained filters to a logical conjunction (AND)
Example:
{$and: [{vertragsnummer: {$contains: {"123"}}, {vertragsnummer: {$notEquals: "100123100"}}, ...]} ≙ select * from tab where vertragsnummer like '%123%' AND vertragsnummer <> '100123100' AND ...
shorthand: {vertragsnummer: {$contains: {"123"}, vertragsnummer: {$notEquals: "100123100"}, ...}

OR

This filter connects the contained filters to a logical disjunction (OR)
Example:
{$or: [{vertragsnummer: {$contains: {"123"}}, {vertragsnummer: {$notEquals: "100123100"}}, ...]} ≙ select * from tab where vertragsnummer like '%123%' OR vertragsnummer <> '100123100' OR ...

Combination of Shorthands

There are shorthands (see above) for both the $and filter and the $equals comparator, which can be combined.
Example:
{name: "Albrecht", vorname: "Klaus"} ≙ select * from tab where name='Albrecht' AND vorname='Klaus'

Nesting

$and, $or and $not filters can be nested in any way.
Example:
{$or: [{$and: [filter1, filter2], $not: {$or: [filter3, filter4]}]} ≙ select * from tab where (filter1 AND filter2) OR NOT(filter3 OR filter4)
nach oben