Skip to main content

Query language

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

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

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.

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

Use double quotes with strings.

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

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

>, <

>=, <=

To compare numeric field values and dates.

CODE 
{count} >= 100

!=

<>

Not equal operators. Both have the same function.

AND

&&

Boolean "and" to combine conditions.

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

OR

||

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

!=

Not equal.

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

( )

Brackets can be used to combine multiple conditions:

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

!

NOT

Negation:

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

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

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

Literal to evaluate if a field is null or not:

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

Numeric operators.

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

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

CODE 
{vat} > 18.5
  
Strings 

.StartsWith

.EndsWith

.Contains

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

Use NOT to negate.

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

To query by string length:

CODE 
{reference}.Length < 10
  
Dates

You can use the DateTime object to compare dates.

 

To compare a date:

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

Or with hours, minutes and seconds:

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

 

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:

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

 

 

String filtering

Basic

 


Do an exact match:

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

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

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

Find null or not null values:

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

Any of

 

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

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

 

 

Number filtering

Basic

Exact numeric match:

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

Range match:

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

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

 

Any of

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

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

Find null values with fields that are nullable:

 

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

 

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:

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

 

Date filtering

Basic

Like with numeric fields, you can do different comparisons:

CODE 
{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

 

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:

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

All deadlines on a day (UTC):

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

All deadlines in a month (UTC):

CODE 
{deadline}.Matches("2018-10", ">=") AND {deadline}.Matches("2018-11", "<")
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:

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

All objects created since 1 year and 6 months:

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

 

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:

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

All objects created since 1 year and 6 months:

CODE 
{created}.Matches("-1y 6m", ">=")
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:

CODE 
{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 "!=".

 

 

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:

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

Write like this:

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


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

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

 

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.

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

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

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

 

 

Label field

The available query options are:

Filter label options

Write like this:

CODE 
{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:

CODE 
{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:

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

Write like this:

CODE 
{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:

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

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

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

When specifying a single value, the quotes are optional.

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

 

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:

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

 

Result includes:

CODE 
"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": ">"
                    }
                ]
            }
        ]
    }

 

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

 

 

 

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close