Query Format

Jira-select queries are written in a YAML format, but using section names inspired by SQL.

Here’s a simple example that will return all Jira issues assigned to you:

select:
- Issue Key: key
- Issue Summary: summary
from: issues
where:
- assignee = "your-email@your-company.net"

Here’s a query that uses many more of the possible sections, but know that in real life, you’re very unlikely to use them all at once:

select:
  My Assignee: assignee
  Key Length: len(key)
from: issues
expand:
- changelog
where:
- project = "MYPROJECT"
order_by:
- created
filter:
- customfield10010 == 140
group_by:
- assignee
having:
- len(key) > 5
sort_by:
- len(key) desc
limit: 100
cap: 10
cache: 86400

Below, we’ll go over what each of these sections are for in detail.

Query Structure Overview

Jira-select Query Sections

	Use	Type	Evaluated where?	Can use custom functions?	Can use query parameters?
select	Defines data to return	List[Expression]	Local	Yes	Yes
from	Defines data source	str	n/a	n/a	No
where	Remote filtering of results	List[JqlString] (for ‘issues’) or Dict[str, Any] (for ‘boards’ and ‘sprints’)	Remote	No	Yes
order_by	Remote ordering of results	List[JqlString]	Remote	No	No
filter	Local pre-grouping filtering of results	List[Expression]	Local	Yes	Yes
group_by	Grouping returned rows together	List[Expression]	Local	Yes	Yes
having	Local post-grouping filtering of results	List[Expression]	Local	Yes	Yes
sort_by	Local sorting of results	List[Expression]	Local	Yes	Yes
limit	Remote limiting of the count of results	int	Remote	n/a	n/a
expand	Defines Jira issue field expansions	List[str]	Remote	n/a	n/a
cap	Post-having/group_by limiting of results	int	Local	n/a	n/a
cache	Defines how long to cache Jira results	int	Local	n/a	n/a

What is a JqlString

A JqlString is standard Jira JQL. You can find more information about writing JQL in Jira’s JQL documentation.

What is an Expression

An Expression is an expression evaluated by Jira-select. Expressions have access to all functions documented in Query Functions. The variables available for use in your expressions can be determined by using jira-select schema [issues|boards|sprints].

Expressions are (with one caveat) valid Python expressions. The single caveat is that you can use curly braces to quote field names. These curly-brace-quoted fields will be replaced with the actual Jira field name before processing the expression in Python.

For example; if you have a custom field named customfield10010 that has a human-readable name of Story Points, you can create an expression like:

{Story Points} >= 5

this expression will be transformed into:

customfield10010 >= 5

before evaluating the expression in Python.

Ubiquitous

select

This section defines what data you would like to include in your report. It should be a dictionary mapping the column name with the expression you would like to display in that column. This section can use custom functions (see Query Functions for options).

For example:

select:
  My Field Name: somefunction(my_field)

Note

This section supports a handful of formats in addition to the one discussed here that you may find in some documentation or in other examples including:

You can specify columns as a list:

select:
- somefunction(my_field) as "My Field Name"

You can specify a single column as a string:

select: somefunction(my_field) as "My Field Name"

The above formats will be supported for the foreseeable future, but the dictionary-based format discussed outside this box is the preferred format for writing queries.

As a shorthand, if you do not provide a value for your dictionary entry, the dictionary entry’s name will be used as the expression for your column:

select:
  issuetype:
  key:
  summary:
from: issues

In the above example, the fields issuetype, key, and summary will be displayed in columns matching their field name.

If you would like to return all fields values, use the expression * as a stirng value to your select statement:

select: "*"
from: issues

Important

Due to yaml parsing rules, the * expression must be quoted.

from

This section defines what you would like to query. The value should be a string.

There are two query sources currently implemented:

• issues: Searches Jira issues.

• boards: Searches Jira boards.

• sprints: Searches Jira sprints.

Common

where

The where section varies depending upon what kind of data source you are querying from.

issues

This section is where you enter the JQL for your query. This should be provided as a list of strings; these strings will be AND-ed together to generate the query sent to Jira.

where:
- assignee = 'me@adamcoddington.net'

You cannot use custom functions in this section given that it is evaluated on your Jira server instead of locally.

boards

You can provide key-value pairs to limit the returned boards; the following parameters are allowed:

• type: The board type. Known values include ‘scrum’, ‘kanban’, and ‘simple’.

• name: The board name. Returned boards mustinclude the string you provided somewhere in their name.

where:
  name: 'My Board'

sprints

You can provide key-value pairs to limit the returned boards; the following parameters are allowed:

• state: The sprint state. Known values include ‘future’, ‘active’, or ‘closed’.

• board_type: The board type of the board to which this sprint belongs. Known values include ‘scrum’, ‘kanban’, and ‘simple’.

• board_name: The board name of the board to which this sprint belongs. Returned boards mustinclude the string you provided somewhere in their name.

where:
  state: 'active'

Note

This type of query is slow due to the way Jira’s API exposes this type of record. There is no endpoint allowing us to list sprints directly. Instead, we must collect a list of sprints by requesting a list of sprints for each board.

You can improve performance substantially by using the board_type or board_name parameters to limit the number of boards we will need to request sprints for.

order_by

This section is where you enter your JQL ordeirng instructions and should be a list of strings.

You cannot use custom functions in this section given that it is evaluated on your Jira server instead of locally.

group_by

This section is where you can define how you would like your rows to be grouped. This behaves similarly to SQL’s GROUP BY statement in that rows sharing the same result in your group_by expression will be grouped togehter.

For example; to count the number of issues by type that are assigned to you you could run the following query:

select:
  Issue Type: issuetype
  Key Length: len(key)
from: issues
where:
- assignee = "your-email@your-company.net"
group_by:
- issuetype

Note

When executing an SQL query that uses a GROUP BY statement, you will always see just a single value for each column even if that column represents multiple rows’ values.

Unlike standard SQL, in Jira-select column values will always contain arrays of values when your column definition does not use a value entered in your group_by section. If you are surprised about a particular field showing an array holding values that are all the same, try adding that column to your group_by statement, too.

If you would like to perform an aggregation across all returned values, you can provide True in your group_by statement. This works because, for every row, True will evaluate to the same result causing all rows to be grouped together:

select:
  Key Length: len(key)
from: issues
where:
- assignee = "your-email@your-company.net"
group_by:
- True

You can use custom functions in this section.

having

This section is where you can provide filtering instructions that Jql cannot handle because they either require local functions or operate on grouped data.

You can use custom functions in this section.

calculate

Perhaps you have an expression you’d like to calculate once and use multiple times across your query (e.g. multiple times across select columns, or in both select and filter at the same time). You can use the calculate section for performing those calculations once and then referencing their result in other expressions; for example:

select:
  Hours in Progress: round(in_progress_seconds / 3600)
calculate:
  in_progress_seconds: interval_size(interval_matching(issue, status="In Progress") & interval_business_hours(parse_date(created))).total_seconds() / 28800
from: issues
filter:
- in_progress_seconds > 60
expand:
- changelog

The above example will calculate the total amount of time issues were in progress in hours while excluding results where they were in progress for fewer than sixty seconds.

sort_by

This section is where you can provide sorting instructions that Jql cannot handle because they either require local functions or operate on grouped data.

You can use custom functions in this section.

limit

This sets a limit on how many rows will be returned from Jira. See Query Lifecycle to understand where this fits in the query lifecycle.

If you would like to limit the count of rows after group_by and having have reduced the count of rows, use cap instead.

Note

limit is handled by Jira itself, so if you would like to instead limit the number of rows returned after having and grouping expressions have reduced the row count, use cap instead.

cache

This will cache the results returned by Jira for up to the specified number of seconds. This can be very helpful if you are iterating on changes to your group_by or having sections in that you can make minor changes and avoid the slow process of requesting records from jira after every change.

Note that the cache parameter can be in one of two forms:

cache: 86400

In this case, we will cache the results for up to 86400 seconds and will also accept an already-stored cached value that is up to that number of seconds old.

cache: [300, 86400]

In this case, we will cache the results for up to 86400 seconds, but will only accept a cached value that is 300 seconds old or newer.

You can also pass null as the second parameter to allow reading from the cache, but disallow writing a new cached value, or pass null as the first parameter to disallow using an existing cache, but allowing storing a new value.

Note that to take full advantage of caching, you may also want to use the filter feature described below. Using it can let you take better advantage of your cached values.

Unusual

expand

Jira has a concept of “field expansion”, and although by default Jira-select will fetch “all” data, that won’t actually return quite all of the data. You can find more information about what data this will return by reading the Jira documentation covering “Search for issues using JQL (GET)”.

filter

In most cases, using where (pre-grouping/having, processed by Jira) and having (post-grouping) are sufficient. But there are scenarios where you might want to filter rows between these two steps. For example:

• Jql doesn’t provide the functionality you need for filtering your resultset, but you’ll be using a group_by statement, too, and thus can’t just use having; because by that point, the field you need to filter on will have been grouped with others.

• You are using a long cache interval to quickly iterate on your query and do not want to have to update your where expression since changing that will cause us to not use the cached results.

In these cases, you can enter the same sorts of expressions you’d use in a having statement in your filter statement as a sort of local-side equivalent of where.

You can use custom functions in this section.

cap

This sets a limit on how many rows will be returned, but unlike limit is evaluated locally.

This can be used if you want your having or group_by statements to have access to as much data as possible (and thus do not want to use limit to reduce the number of rows returned from Jira), but still want to limit the number of rows in your final document.