If you are a Jira user, one word must have caught your attention – it is JQL. An abbreviation that is strikingly similar to SQL (or Structured query language), which is used in the databases context (for example MySQL, PostgreSQL…).
Making queries
JQL, or (Jira query language) is very similar to the SQL. Firstly, because of the name. It suggest it is a query language. It means that by using this language you are creating a query to display some information or data.
In case of SQL, it is database rows containing the data, in case of JQL it is data from Jira – and in particular Jira issues.
SQL example
SELECT * FROM users WHERE ID = 1
Is an example of a SQL query, that selects a user from the table users that is stored with ID equals to 1.
JQL example
key = AC-1
Is an example of a JQL query, that selects an issue that is stored with its key equals to AC-1.
One big difference between the SQL and JQL is that the SQL query is used to conduct majority of the database operations, for example reading, updating, deleting, truncating…etc. Whereas JQL is here only to read data. This is why the JQL query needn’t start with the database-like clauses (e.g. SELECT * FROM…).
Possibilities in JQL
JQL is a query language used to read issues from Jira. When selecting data, two most important cases are:
- filtering data
- ordering data
Filtering data with JQL
When reading Jira data, in most cases you’re going to be interested in certain issue type, issue status, assignee, project they belong to or maybe a date the issues were created or resolved.
All of those cases can be implemented via JQL search, example:
| Use case | JQL query |
|---|---|
| only Task and Bug issue types | type in (Task, Bug) |
| only In progress issues | status = “In Progress” |
| assigned to me | assignee = currentUser() |
| belonging to projects AC and BG | project in (AC, BG) |
The aforementioned examples are just the most basic queries working with JQL. This should be comprehensible for everybody understanding SQL, but also if you have spreadsheet editor experience (e.g. MS Excel, Apple Numbers…), you should not have a problem at all.
Ordering data with JQL
Not forgetting the other important factor when retrieving data, ordering. Ordering data is important for lots of use cases, for example:
- large data set – if your Jira instance stores around 100,000 (a hundred-thousand) issues and you try to pick only tasks (type = Task), then the paginated results contain only 50 or 100 results. And what is displayed on the first, the second or the last page is the matter of the ordering
- visual representation – if you are retrieving for example all resolved issues, it might be handy to order them by the resolution date, so as it is easier to read the data set
ORDER BY type
ORDER BY created DEAC
ORDER BY summary ASC
ORDER BY resolutiondate DESC
Those are just a few options on how the ordering can be used
Putting it all together
Filtering and ordering are very important concepts. But how to use them together? Well, this is the easiest step when working with JQL queries. You can simply join the two queries by a single space, for example:
type in (Task, Bug) AND resolutiondate >= '2024-01-01' ORDER BY resolutiondate DESC
How to use JQL in Jira
One of the most probable places, where you’ll be working with filters in Jira – is Filters. It is a section in Jira, that allows you to filter specific Jira issues and save the filter for later usage.
To learn more about Filters in Jira, follow the link: https://support.atlassian.com/jira-software-cloud/docs/manage-filters/.
Is there more for JQL?
Yes. JQL might seem like a simple query language to retrieve issues. But it contains a wide range of possibilities. It offers not only simple filtering options, but also complicated selected, functions, custom operators and so on.
If you’re interested in other, more advanced usage of JQL, certainly check out the official documenation here: https://support.atlassian.com/jira-software-cloud/docs/jql-fields/.