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

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} == nullNOT {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", "<")
...

These operators are still under construction

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: >, <, >=, <=, =, !=

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

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.