# Filter

<div id="bkmrk-the-filter-parameter">The filter parameter in the ##### loadrecords 

and 

##### loadobjecttypes 

functions is a complex JSON object representing the query criteria.</div><div id="bkmrk-if-the-filter-is-omi">If the filter is omitted, no filter is applied.</div><div id="bkmrk-a-filter-object-can-">A filter object can have different characteristics and also contain other filter objects. Multiple nested filters can therefore be defined.</div>### Filter classes  


### Comparer

<div id="bkmrk-this-filter-performs"><div>This filter performs one or more comparison operations on a field.</div><div>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:</div><div><code>&#123;field name&#58; &#123;operator&#58; value&#125;&#125;</code></div><div>Some comparison operations expect 2 or more comparison values. These are put into an array:</div><div><code>&#123;field name&#58; &#123;operator&#58; &#91;value1&#44; value2&#44; &#46;&#46;&#46;&#93;&#125;&#125;</code></div><div>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)</div><div><code>&#123;field name&#58; &#91;&#123;operator1&#58; value1&#125;&#44;&#123;operator2&#58; value2&#125;&#44;&#46;&#46;&#46;&#93;&#125;</code></div><div>Example:</div><div><code>&#123;&#36;system&#95;id&#58; &#123;&#36;equals&#44; &#34;123456&#34;&#125;&#125;  ≙ select &#42; from tab where system&#95;id&#61;&#39;123456&#39;</code></div></div><div id="bkmrk-"><code>&#60;br&#62;&#60;&#47;br&#62;</code></div><div id="bkmrk-comparison-operation">#### comparison operations

<div><table style="width: 100%; border-collapse: collapse;"><tbody><tr><td>**Operator**</td><td>**Value (data type)**</td><td>**Description**</td><td>**Example**</td><td>**corresponds to this SQL query**</td></tr><tr><td>$isEmpty</td><td>bool</td><td>true only; field has empty value; identical to $isNull</td><td>{system\_id: {$isEmpty: true}}</td><td>system\_id IS NULL</td></tr><tr><td>$notEmpty</td><td>bool</td><td>true only; field has no empty value</td><td>{system\_id: {$notEmpty: true}}</td><td>system\_id&lt;&gt;''</td></tr><tr><td>$isNull</td><td>bool</td><td>true only; field value is NULL</td><td>{system\_id: {$isNull: true}}</td><td>system\_id IS NULL</td></tr><tr><td>$notNull</td><td>bool</td><td>true only; field value is not NULL</td><td>{system\_id: {$notNull: true}}</td><td>system\_id IS NOT NULL</td></tr><tr><td>$equals</td><td>any</td><td>field value equals comparison value</td><td>{system\_id: {$equals: "7"}}  
**shorthand**: {system\_id: "7"}</td><td>system\_id='7'</td></tr><tr><td>$notEquals</td><td>any</td><td>field value not equal to comparison value</td><td>{system\_id: {$notEquals: "7"}}</td><td>system\_id&lt;&gt;'7'</td></tr><tr><td>$endswith</td><td>string</td><td>field value ends with comparison value</td><td>{system\_id: {$endswith: "7"}}</td><td>system\_id LIKE '%7'</td></tr><tr><td>$startswith</td><td>string</td><td>field value starts with comparison value</td><td>{system\_id: {$startswith: "7"}}</td><td>system\_id LIKE '7%'</td></tr><tr><td>$contains</td><td>string</td><td>fiel value contains comparison value</td><td>{system\_id: {$contains: "7"}}</td><td>system\_id LIKE '%7%'</td></tr><tr><td>$like</td><td>string</td><td>field value matches the pattern</td><td>{system\_id: {$like: "%7\_7%"}}</td><td>system\_id LIKE '%7\_7%'</td></tr><tr><td>$in</td><td>any\[\]</td><td>field value equals one of the comparison values</td><td>{system\_id: {$in: \["7","8","9"\]}}</td><td>system\_id IN ('7','8','9')</td></tr><tr><td>$notIn</td><td>any\[\]</td><td>field value not equal to any of the comparison values</td><td>{system\_id: {$notIn: \["7","8","9"\]}}</td><td>system\_id NOT IN ('7','8','9')</td></tr><tr><td>$between</td><td>any\[2\]</td><td>field value lies between the two comparison values</td><td>{system\_id: {$between: \["7","9"\]}}</td><td>system\_id BETWEEN '7' AND '9'</td></tr><tr><td>$notBetween</td><td>any\[2\]</td><td>field value not lies between the two comparison values</td><td>{system\_id: {$notBetween: \["7","9"\]}}</td><td>system\_id NOT BETWEEN '7' AND '9'</td></tr><tr><td>$gt</td><td>any</td><td>field value is greater than comparison value</td><td>{system\_id: {$gt: "7"}}</td><td>system\_id &gt; '7'</td></tr><tr><td>$gte</td><td>any</td><td>field value is greater than or equal to comparison value</td><td>{system\_id: {$gte: "7"}}</td><td>system\_id &gt;= '7'</td></tr><tr><td>$lt</td><td>any</td><td>field value is less than comparison value</td><td>{system\_id: {$lt: "7"}}</td><td>system\_id &lt; '7'</td></tr><tr><td>$lte</td><td>any</td><td>field value is less than or equal to comparison value</td><td>{system\_id: {$lte: "7"}}</td><td>system\_id &lt;= '7'</td></tr><tr><td>$equals\_field</td><td>string</td><td>field value is equal to specified other field</td><td>{system\_last\_change\_at: {$equals\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at = system\_created\_at</td></tr><tr><td>$notEquals\_field</td><td>string</td><td>field value not equal to specified other field</td><td>{system\_last\_change\_at: {$notEquals\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at &lt;&gt; system\_created\_at</td></tr><tr><td>$gt\_field</td><td>string</td><td>field value is greater than specified other field</td><td>{system\_last\_change\_at: {$gt\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at &gt; system\_created\_at</td></tr><tr><td>$gte\_field</td><td>string</td><td>field value is greater than or equal to specified other field</td><td>{system\_last\_change\_at: {$gte\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at &gt;= system\_created\_at</td></tr><tr><td>$lt\_field</td><td>string</td><td>field value is less than specified other field</td><td>{system\_last\_change\_at: {$lt\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at &lt; system\_created\_at</td></tr><tr><td>$lte\_field</td><td>string</td><td>field value is less than or equal to specified other field</td><td>{system\_last\_change\_at: {$lte\_field: "system\_created\_at"}}</td><td>system\_last\_change\_at &lt;= system\_created\_at</td></tr><tr><td>$between\_field</td><td>string\[2\]</td><td>field value lies between the values of two other specified fields</td><td>{system\_last\_change\_at: {$between\_field: \["system\_created\_at","system\_delete\_at"\]}}</td><td>system\_last\_change\_at BETWEEN system\_created\_at AND system\_delete\_at</td></tr><tr><td>$notBetween\_field</td><td>string\[2\]</td><td>field value not lies between the values of two other specified fields</td><td>{system\_last\_change\_at: {$notBetween\_field: \["system\_created\_at","system\_delete\_at"\]}}</td><td>system\_last\_change\_at NOT BETWEEN system\_created\_at AND system\_delete\_at</td></tr><tr><td>$date</td><td>int</td><td>Date/Time only: day of month equals comparison value</td><td>{system\_created\_at: {$date: 1}}</td><td>DAY(system\_created\_at) = 1</td></tr><tr><td>$day</td><td>int</td><td>Date/Time only: weekday equals comparison value</td><td>{system\_created\_at: {$day: 1}}</td><td>DATEPART(dw, system\_created\_at) = 1</td></tr><tr><td>$year</td><td>int</td><td>Date/Time only: year equals comparison value</td><td>{system\_created\_at: {$year: 2000}}</td><td>YEAR(system\_created\_at)=2000</td></tr><tr><td>$month</td><td>int</td><td>Date/Time only: month equals comparison value</td><td>{system\_created\_at: {$month: 12}}</td><td>MONTH(system\_created\_at)=12</td></tr><tr><td>$minutes</td><td>int</td><td>Date/Time only: minute equals comparison value</td><td>{system\_created\_at: {$minutes: 59}}</td><td>DATEPART(mi, system\_created\_at) = 59</td></tr><tr><td>$hours</td><td>int</td><td>Date/Time only: hour equals comparison value</td><td>{system\_created\_at: {$hours: 18}}</td><td>DATEPART(hh, system\_created\_at) = 18</td></tr></tbody></table>

</div></div><div id="bkmrk-alldefault-filter.-t">### ALL

<div>**Default filter**. This filter loads all records without restrictions.</div><div>*Example:*</div><div><code>&#123;&#36;all&#58; true&#125;</code> ≙ <code>select &#42; from tab where 1&#61;1</code></div><div>only true is allowed</div>### EMPTY

<div>This filter creates an empty query. Usually not required.</div><div>*Example:*</div><div><code>&#123;&#36;empty&#58; true&#125;</code> ≙ <code>select &#42; from tab where 1&#61;0</code></div><div>only true is allowed</div>### NOT

<div>This filter negates the included filter</div><div>*Example:*</div><div><code>&#123;&#36;not&#58; &#123;system&#95;id&#58; &#34;7&#34;&#125;&#125;</code> ≙ <code>select &#42; from tab where NOT &#40;sytem&#95;id&#61;&#39;7&#39;&#41;</code></div>### AND

<div>This filter connects the contained filters to a logical conjunction (AND)</div><div>*Example:*</div><div><code>&#123;&#36;and&#58; &#91;&#123;vertragsnummer&#58; &#123;&#36;contains&#58; &#123;&#34;123&#34;&#125;&#125;&#44; &#123;vertragsnummer&#58; &#123;&#36;notEquals&#58; &#34;100123100&#34;&#125;&#125;&#44; &#46;&#46;&#46;&#93;&#125;</code> ≙ <code>select &#42; from tab where vertragsnummer like &#39;&#37;123&#37;&#39; AND vertragsnummer &#60;&#62; &#39;100123100&#39; AND &#46;&#46;&#46;</code></div><div>**shorthand:** <code>&#123;vertragsnummer&#58; &#123;&#36;contains&#58; &#123;&#34;123&#34;&#125;&#44; vertragsnummer&#58; &#123;&#36;notEquals&#58; &#34;100123100&#34;&#125;&#44; &#46;&#46;&#46;&#125;</code></div>### OR

<div>This filter connects the contained filters to a logical disjunction (OR)</div><div>*Example:*</div><div><code>&#123;&#36;or&#58; &#91;&#123;vertragsnummer&#58; &#123;&#36;contains&#58; &#123;&#34;123&#34;&#125;&#125;&#44; &#123;vertragsnummer&#58; &#123;&#36;notEquals&#58; &#34;100123100&#34;&#125;&#125;&#44; &#46;&#46;&#46;&#93;&#125;</code> ≙ <code>select &#42; from tab where vertragsnummer like &#39;&#37;123&#37;&#39; OR vertragsnummer &#60;&#62; &#39;100123100&#39; OR &#46;&#46;&#46;</code></div>### Combination of Shorthands

<div>There are shorthands (see above) for both the $and filter and the $equals comparator, which can be combined.</div><div>*Example:*</div><div><code>&#123;name&#58; &#34;Albrecht&#34;&#44; vorname&#58; &#34;Klaus&#34;&#125;</code> ≙ <code>select &#42; from tab where name&#61;&#39;Albrecht&#39; AND vorname&#61;&#39;Klaus&#39;</code></div>### Nesting

<div>$and, $or and $not filters can be nested in any way.</div><div>*Example:*</div><div><code>&#123;&#36;or&#58; &#91;&#123;&#36;and&#58; &#91;&#60;i&#62;filter1&#60;&#47;i&#62;&#44; &#60;i&#62;filter2&#60;&#47;i&#62;&#93;&#44; &#36;not&#58; &#123;&#36;or&#58; &#91;&#60;i&#62;filter3&#60;&#47;i&#62;&#44; &#60;i&#62;filter4&#60;&#47;i&#62;&#93;&#125;&#93;&#125;</code> ≙ <code>select &#42; from tab where &#40;&#60;i&#62;filter1 &#60;&#47;i&#62;AND &#60;i&#62;filter2&#60;&#47;i&#62;&#41; OR NOT&#40;&#60;i&#62;filter3 &#60;&#47;i&#62;OR &#60;i&#62;filter4&#60;&#47;i&#62;&#41;</code></div></div>