Kantree Query Language (KQL)

Search through cards using our simple & powerful query language

Kantree is shipped with a powerful query langage, that let you filter or search for cards or calculate things from them.

You can use it in multiple places:

  • in the search box in topbar,
  • in the filter box in project topbar,
  • in project reports,
  • in formula card fields

Searching through cards

To search through card titles, just use the keywords you want to look for. There are three keywords which you cannot use directly as they are reserved: and, or, not. If you want to look for them in titles, you can surround them by double quotes (eg. oranges "and" berries).

To search for cards not matching keywords, prepend the keyword by not. (eg. not oranges). If you need to exclude cards matching a sentence, surround the keywords with double quotes (eg. not "oranges berries").

To search for a card with a specific reference, use # followed by the reference number (eg. #123).

To search for a card which is in a group, use # followed by the group name. If your group name contains spaces, just ignore them (eg. search for cards in “List 2”, use #list2).

To search for a card which has a member in any of its fields, use @ followed by the username (eg. @username). You can use @me to reference the currently logged in user.

Usernames and group names cannot contain dots or spaces. To use names with these characters, surround the name by curly braces (eg. @{user name} and #{List 2}).

Fields

You can search cards by their fields using either of the following syntax: field=value or {field}=value. The former can only be used if there are no spaces or special characters in the field name. Otherwise use the latter (eg. {field with space}=value).

Available operators are:

  • =: equals (eg. {Contact} = "john@doe.com")
  • !=: different (eg. {Estimated hours} != 10)
  • > or >=: greater than or greater or equal than (eg. {Estimated hours} > 10)
  • < or <=: less than or less or equal than (eg. {Estimated hours} <= 10)
  • ~=: fuzzy match (for text values). (eg. description ~= orange)

If the field is a list of values (eg. members field), use the in operator: @username in Assignees.

When comparing a field, values can be:

  • an empty value (meaning the fields has not been set): empty (eg. finding all cards with an empty description: description=empty)
  • a number: 10 or 12.1 or -3.4
  • a string: word or "multiple words are surrounded by double quotes"
  • yes or no (for yes/no fields)
  • a date: a double quoted string using the format: YYYY-MM-DD (eg. "2017-02-05"). A few special keywords which can be used as a date exist: today, tomorrow, yesterday. (eg. {due date}<today)
  • a member: @username
  • a card reference: #123
  • an object reference (see further)

You can also use arithmetic expressions (eg. 3 + 4).

A few special fields exist:

  • ref: card reference (eg. ref=123)
  • title: card title (eg. title="my task")
  • created at: card creation date (eg. {created at}="2017-02-01")
  • created by: user who created the card (eg. {created by}=@me)
  • updated at: date of the last update
  • state: card state (eg. state=completed)
  • started at: when a card has a state of in progress, the date at which it was started.
  • finished at: when a card has a state of dropped, completed or closed, the date at which it was finished.
  • resolution time: the time between finished at and started at
  • archived: if the card is archived (yes/no value: archived=yes)
  • parent: parent card id (used in query like {parent} in {relationship field 1} or {parent}=#14)
  • hlevel: level of the card in the hierarchy of the project. For instance, for a sub-card, it will return 2
  • nb children: number of children
  • nb comments: number of comments
  • last comment at: date of the last comment
  • model: model name (eg. model=bug)
  • project: project name (eg. project="My Project")
  • team: team’s name
  • form: form’s name (if the card has been created through a project form)

You can use the keyword not in front of any condition to match for cards not meeting the condition (eg. not @me in assignees).

Combining conditions

You can use multiple conditions in your queries. Just put them one after the other. All conditions must be met for a card to match.

For more flexible queries, you can combine conditions using logical operators:

  • and: the different operands are all mandatory
  • or: any of the operand can match

Like mentioned before, if no logical operators are provided, and is used. You can group conditions between parentheses. and has priority over or.

Advanced usage

Functions

There exist two types of functions:

  • functions which return a value (eg. now())
  • conditional functions which are used as a condition (eg. date?("week"))

Available functions:

  • if(expression, value, elseValue): returns value if expression is true and elseValue if expression is false
  • now(): returns the current date
  • ago(interval): returns now() - interval. interval is a string containing a number followed by a unit (days, weeks, months, years, …)
  • ahead(interval): same as ago(), but with now() + interval
  • period(interval): returns an amount of time to add to an existing date, for instance now() + period("1 week")
  • today(): returns today’s date
  • week_start(): returns the exact date of the beginning of the week
  • week_end(): returns the exact date of the end of the week
  • workweek_start(): returns the exact date of the beginning of the work week
  • workweek_end(): returns the exact date of the end of the work week
  • month_start(): returns the exact date of the beginning of the month
  • month_end(): returns the exact date of the end of the month
  • year_start(): returns the exact date of the beginning of the year
  • year_end(): returns the exact date of the end of the year
  • count_days(start, stop): returs the number of days between start and stop dates.
  • count_working_days(start, stop): returns the number of working days between start and stop dates
  • as_string(node): returns a string from a node that isn’t one, for instance as_string({Points})
  • as_date(node): returns a date from a node that isn’t one, for instance as_date({Text field})
  • as_number(node): returns a number from a node that isn’t one, for instance as_number({Text field})
  • size(list): returns the number of items in a list, for instance size(assignees)
  • substring(node, start, count): returns a part of an other field starting from start index and taking count characters

Example: get the cards which have a due date 3 days ago: {due date} <= ago("3 days")

Available conditional functions:

  • full_text_search?(text): search though titles, fields and comments for text
  • search_comments?(text): search only through comments
  • mentions?(@username): search for mentions of the specified user through fields and comments
  • is_in_active_group?(): checks if the card is in a group which has dates and where today is in the date range.
  • is_in_group?(group_name, type_name): checks if the card is in group group_name (type_name optional).
  • was_in_group?(group_name, type_name): checks if the card has already been in group group_name (type_name optional).
  • is_in_group_type?(type_name): checks if the card is in group type type_name.
  • date?(date): checks if any fields of type date match the specified date. Examples of valid date queries: “05-27-2018” (MM-DD-YYYY format), “week”, “month”, “year”, “week+1” (for next week), “week-1” (for last week), “week-2” (for second to last week)

Object references

Object references can be used to fetch a value from a card or a user. It can be used in arithmetic expressions or in field comparisons.

To get a field value from a card, use the syntax: {#ref:field} (eg. {#123:Estimated hours}).

To get a field value from a user, use the syntax: {@username:field} (eg. {@me:email}). The following fields are available for users: email, username.

Examples

Find all cards which are assigned to me (using the Assignees field):

@me in assignees

Find all cards assigned to me in the Backlog group:

@me in assignees #backlog

Find all cards which are due tomorrow:

{due date}=tomorrow

Find all cards which have “issue” in their description:

description ~= issue

Find all cards which I’m assigned to or that I’ve created:

@me in assignees or {created by}=@me

Find all cards which are overdue and that have no assignees:

assignees=empty {due date}<today

Find all cards which are more than 3 days old with no description:

description=empty {created at} > ago("3 days")

Find all cards using the Bug model which are more than 1 week old:

model=bug {created at} > ago("1 week")