Query language

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.