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", ">=")
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
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
|
!= <> | Not equal operators. Both have the same function. |
AND && | Boolean "and" to combine conditions. {count} >= 100 && {count} < 1000
{count} >= 100 AND {count} < 1000
|
OR || | {count} < 100 || {count} > 1000
{count} < 100 OR {count} > 1000
|
!= | 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
|
| x ? y : z | Evaluates y if x is true, evaluates z if x is false. {count} >= ({status} = 1 ? 100 : 1000)
|
| null | Literal to evaluate if a field is null or not: {deadline} == null
NOT {deadline} == null
|
| Numbers | |
| + - * / | Numeric operators. ({words} * {document} > 1000)
|
| 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")
|
| .Length | To query by string length: {reference}.Length < 10
|
| Dates | 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)
|
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", "<")
...
String filtering
Basic
| Do an exact match: {reference}.Matches("123")
|
| 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")
|
| Null values | 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"))
|
Number filtering
| Basic | 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: >, <, >=, <=, =, !=
|
| Any of | This construct matches if the field matches any one of the pipe separated values: {status}.Matches("1,2,5", "anyof")
|
| Null values | Find null values with fields that are nullable:
{words}.Matches(null)
{words}.Matches(null, "!=")
|
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", "!=")
Important:
|
| Date formats | Formats that are supported are:
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, "!=")
|
| Filter day or month | 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", "<")
|
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
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
|
| Find any option | Write like this: {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:
{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. {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. {field}.Matches(false) // Value xp must be 0
|
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
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
|
| Exclude label options | 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
|
| Any label option | To filter for data where the label has any of the options: {cffield}.Matches(null, "!=") // Any label
|
| Comments | When specifying a single value, the quotes are optional. {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:
/jobs/list/full
{ "query": '{created}.Matches("2018-03-13", ">=", "2018-03-15", "<") AND {segments}.Matches(100, ">")', "take": 10 }
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": ">"
}
]
}
]
}
This is also helpful to see how dates and other values are interpreted.