Query language

=

==

Compare field with a value. You can use both "=" and "==". These and other operators can be used with most field types.

{reference} = "1223-82"
{id} = 10234

Use double quotes with strings.

Inside a string literal, a double quote is written as two consecutive double quotes:

{reference} = "Reference ""123"""

>, <

>=, <=

To compare numeric field values and dates.

{count} >= 100

!=

<>

AND

&&

Boolean "and" to combine conditions.

{count} >= 100 && {count} < 1000
{count} >= 100 AND {count} < 1000

OR

||

!=

Not equal.

{count} != 100
{reference} != "alpha"

( )

Brackets can be used to combine multiple conditions:

({count} == 100 OR {count} == 101) AND {status} == 10

!

NOT

Negation:

(NOT {count} == 100) OR {count} == 101
 
is equal to:
{count} != 100 OR {count} == 101

Evaluates y if x is true, evaluates z if x is false.

{count} >= ({status} = 1 ? 100 : 1000)

Literal to evaluate if a field is null or not:

{deadline} == null
NOT {deadline} == null

Numeric operators.

({words} * {document} > 1000)

Decimal fields use the optional '.' (dot) decimal point:

{vat} > 18.5

.StartsWith

.EndsWith

.Contains

Various operators to search string fields by prefix, suffix or infix.

Use NOT to negate.

{reference}.StartsWith("123")
{reference}.EndsWith("123")
{reference}.Contains("123")
NOT {reference}.Contains("123")

To query by string length:

{reference}.Length < 10

You can use the DateTime object to compare dates.

To compare a date:

{deadline} >= DateTime(2007, 1, 1)

Or with hours, minutes and seconds:

{deadline} < DateTime(2007, 1, 1, 10, 30, 0)

Basic

Do an exact match:

{reference}.Matches("123")

Match types: exact, infix, prefix or suffix match:

{reference}.Matches("123", "=")
{reference}.Matches("123", "prefix")
{reference}.Matches("123", "suffix")
{reference}.Matches("123", "infix")

Find null or not null values:

{reference}.Matches(null)
{reference}.Matches(null, "!=")

Any of

Specify a list of values where at least one or all strings must match exactly:

{reference}.Matches("option1|option2|option3", "anyof") 

This query is equivalent to a boolean OR on an exact match of each string.

The above query is equivalent to:

(({reference} = "o1") OR ({reference} = "o2") OR ({reference} = "o3"))

Exact numeric match:

{words}.Matches(100)
{words}.Matches(100, "=")    // "=" is the default operator
{words}.Matches(100, "!=")   // Different from 100

Range match:

{words}.Matches(100, ">=", 500, "<=")

The operators can be any of: >, <, >=, <=, =, !=

This construct matches if the field matches any one of the pipe separated values:

{status}.Matches("1,2,5", "anyof")

Find null values with fields that are nullable:

{words}.Matches(null)
{words}.Matches(null, "!=")

You can use all the features also available with number filters.

Please always use the "." (dot) character for the decimal point. The decimal point is optional.

Examples:

{vat}.Matches(19.5)
{vat}.Matches(18, ">")    // "=" is the default operator

Like with numeric fields, you can do different comparisons:

{deadline}.Matches("2018", ">=")
{deadline}.Matches("2018-10-10", ">=")
{deadline}.Matches("2018-10-10", ">=", "2019-2-2", "<")
{deadline}.Matches("2018-12-25:10:45:20Z", "!=")

Important:

• Dates are always interpreted as UTC.
• Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00
• See below for permitted date formats

Formats that are supported are:

• • 2018
  • 2018-10
  • 2018-10-25
  • 2018-10-25 22:30:00 or 2018-10-25T22:30:00 or 2018-10-25T22:30:00Z
  • 2018-10-25 22:30:00.123 (append fraction of seconds) + variants as before
  • Full round trip format: 2009-06-15T13:45:30.0000000Z

Note: Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00

Find null values with fields that are nullable:

{deadline}.Matches(null)
{words}.Matches(null, "!=")

All deadlines on a day (UTC):

{deadline}.Matches("2018-10-25", ">=") AND {deadline}.Matches("2018-10-26", "<")

All deadlines in a month (UTC):

{deadline}.Matches("2018-10", ">=") AND {deadline}.Matches("2018-11", "<")

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

• "1y 2m" : 1 year and 2 months into the future
• "-2d": 2 days into the past
• "+4 M": 4 minutes into future
• "24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

You can thus use one or more combinations of:

• y, m, d: Year, Month, Day
• H, M, S: Hours, Minutes, Seconds

Deadline within +/- 6 hours:

{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

{created}.Matches("-1y 6m", ">=")

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

• "1y 2m" : 1 year and 2 months into the future
• "-2d": 2 days into the past
• "+4 M": 4 minutes into future
• "24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

Deadline within +/- 6 hours:

{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

{created}.Matches("-1y 6m", ">=")

You can thus use one or more combinations of:

• y, m, d: Year, Month, Day
• H, M, S: Hours, Minutes, Seconds

You can prefix offset with "-" for dates into the past: -1y stands for one year ago.

You can prefix with optional "+" for dates into the future: +1y (or 1y) stands for one year from now.

Valid examples:

{mybool}.Matches(true)
{mybool}.Matches(false)
{mybool}.Matches(null)             // Field is not set (null)
{mybool}.Matches(null, "!=")       // Fiield is set and either true or false

Permitted operators are: "=" and "!=".

Write like this:

{cffield}.Matches("o1|o2|o3", "allof")  // All 3 options must appear in the field

Write like this:

{cffield}.Matches("o1|o2|o3", "anyof")  // Any one of the options must appear

This filter returns all objects that have at least one label that was explicitly assigned by a user.

{field}.Matches(true)  // Value xp must be greater than 0

This filter returns all objects with no label assigned by a user.

{field}.Matches(false)  // Value xp must be 0

Write like this:

{labelfield}.Matches(0)  // First label value option selected

{labelfield}.Matches(2)  // Third label value option selected
{labelfield}.Matches("1,2,3")  // Any of options 1, 2 or 3 selected

You can also use the optional operator:

{labelfield}.Matches(2, "=")  // Third label value option selected
{labelfield}.Matches("1,2,3", "=")  // Any of options 1, 2 or 3 selected

If a label is not shown by default, it can also have the "null" option:

{cffield}.Matches(null)  // No label set

Write like this:

{cffield}.Matches("3,4,5", "!=")  // None of the options must appear

If a label is not shown by default, it can also have the "null" option:

{cffield}.Matches("null, 0", "!=")  // No label or just default label

To filter for data where the label has any of the options:

{cffield}.Matches(null, "!=")  // Any label

When specifying a single value, the quotes are optional.

{cffield}.Matches("3", "=")
or
{cffield}.Matches(3, "=")