The query language ressembles the SQL language and allows powerful queries. A typical example would be:

{status} = 1 AND {reference}.StartsWith("2010") AND {deadline}.Matches("2018-10", ">=")
CODE

Fields are enclosed in curly brackets. To get a list of all available and queryable fields with ID, name and description, use this method.

 

Jump to a section:

Basic Features

The query language is very rich. The most important features are listed below:

General features 

=

==

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
CODE

Use double quotes with strings.

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

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

>, <

>=, <=

To compare numeric field values and dates.

{count} >= 100
CODE

!=

<>

Not equal operators. Both have the same function.

AND

&&

Boolean "and" to combine conditions.

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

OR

||

{count} < 100 || {count} > 1000
{count} < 100 OR {count} > 1000
CODE

!=

Not equal.

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

( )

Brackets can be used to combine multiple conditions:

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

!

NOT

Negation:

(NOT {count} == 100) OR {count} == 101
 
is equal to:
{count} != 100 OR {count} == 101
CODE
x ? y : z

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

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

Literal to evaluate if a field is null or not:

{deadline} == null
NOT {deadline} == null
CODE
  
Numbers 
+ - * /

Numeric operators.

({words} * {document} > 1000)
CODE
Decimals

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

{vat} > 18.5
CODE
  
Strings 

.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")
CODE
 .Length

To query by string length:

{reference}.Length < 10
CODE
  
Dates

You can use the DateTime object to compare dates.

 

To compare a date:

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

Or with hours, minutes and seconds:

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

 

Recommended "Matches" keyword

We strongly recommend using the following construct for all your filtering requirements.

It takes away most of the complexity of properly formatting and evaluating different field types, including dates, multi-select custom fields, labels and advanced string searches.

Examples:

{myfield}.Matches("hello", "prefix")
{myfield}.Matches("2018-1-1", ">=")
{myfield}.Matches(10, ">=", 100, "<")
{myfield}.Matches("world") AND {mydate}.Matches("2018-1-1", ">=", "2018-1-2", "<")
...
CODE

 

 

String filtering

Basic

 


Do an exact match:

{reference}.Matches("123")
CODE
Infix, Prefix, Suffix

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

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

Find null or not null values:

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

Any of

 

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

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

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"))

 

 

Number filtering

Basic

Exact numeric match:

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

Range match:

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

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

 

Any of

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

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

Find null values with fields that are nullable:

 

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

 

Decimals filtering

Summary

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
CODE

 

Date filtering

Basic

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", "!=")
CODE

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

 

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

Null values

Find null values with fields that are nullable:

{deadline}.Matches(null)
{words}.Matches(null, "!=")
CODE
Filter day or month

All deadlines on a day (UTC):

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

All deadlines in a month (UTC):

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

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", "<=")
CODE

All objects created since 1 year and 6 months:

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

 

Date offset filtering

  
Date offsets

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", "<=")
CODE

All objects created since 1 year and 6 months:

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

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.

 

Boolean filtering

Basic

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
CODE

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

 

 

Custom field multi select picklists

Multi-select pick list custom fields store selected values in a very specific format:

  • "|Austria|France|Germany|"

Selected options are pipe delimited.

 

Find all options

Write like this:

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

Write like this:

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


Comments: Whenever the field is a multi-select picklist, the system will automatically adjust the query. For the example above the query wil be:

{cffield}.Matches("o1|o2|o3", "anyof")
== translated to ==>
{cffield}.Contains("|o1|") OR {cffield}.Contains("|o2|") OR {cffield}.Contains("|o3|")
CODE

 

Labels count field

The labels count field returns the total labels assigned to an object such as a job or project.

It contains 2 properties:

  • cnt: Total assigned labels.
  • xp: Total explicitly set labels. This excludes labels that are configured to automatically show up on ALL objects with a default value.

 

The available query options are:

Filter for xp > 0

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
CODE
Find for xp = 0

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

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

 

 

Label field

The available query options are:

Filter label options

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
CODE

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
CODE

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

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

Write like this:

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

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
CODE
Any label option

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

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

When specifying a single value, the quotes are optional.

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

 

Query tree

When running queries the results may include a JSON representation of your query.

This happens whenever your query string is composed of just:

  • .Matches() clauses 
  • "AND"
  • "("
  • ")"

 

Sample query:

/jobs/list/full
{ "query": '{created}.Matches("2018-03-13", ">=", "2018-03-15", "<") AND {segments}.Matches(100, ">")', "take": 10 }
CODE

 

Result includes:

"querytree": {
        "and": [
            {
                "field": "created",
                "params": [
                    {
                        "value": "2018-03-13T00:00:00Z",
                        "op": ">="
                    },
                    {
                        "value": "2018-03-15T00:00:00Z",
                        "op": "<"
                    }
                ]
            },
            {
                "field": "segments",
                "params": [
                    {
                        "value": 100,
                        "op": ">"
                    }
                ]
            }
        ]
    }
CODE

 

This is also helpful to see how dates and other values are interpreted.