JQL stands for JIRA Query Language and is not to be confused with Java Query Language. It’s the most flexible way to search for issues in JIRA. Another benefit? JQL is for everyone: developers, testers, project managers, as well as business users. This blog is intended to be a tutorial for those who have no experience with database queries to those who want faster access to information in JIRA. So basically if you work in JIRA this blog is for you.
If you are a developer who knows SQL
Atlassian’s implementation of JQL is very similar to SQL. The search box is fully equipped with code hints and inline validation to make constructing queries easy. To try out JQL in your JIRA instance, you can:
- Log into JIRA
- Click Issues
- Click Edit (If you have a search in progress)
- Click Advanced Searching
You can then type your queries directly into JIRA. For a complete reference, go to Advanced Searching. I recommend bookmarking this link, because I find myself returning to it often to look for more obscure queries. This page has a lot of content, so it’s helpful to go here and do a “Ctrl/Cmd + F” and search to jump directly to a specific topic or keyword of interest.
What about the rest of us?
JQL is equally as awesome for non-technical users. Let’s start with a simple example to help explain fundamental concepts to ground our discussion. If you stepped into an unfamiliar grocery store, you might ask an employee, “Where can I find a package of three Acme cookies?” The clerk would bring you to the right aisle and shelf for that product. JIRA can do the same for issues.
There are two types of searches in JIRA: simple and advanced. Simple search uses a set of forms that a user fills in. The Advanced Searching uses JQL, our very own query language. Not familiar with queries? Queries are a series of simple questions strung together to form a more complex question. A query has three basic parts: fields, operators, and values. You can optionally link them together using a few select keywords. Let’s define each term for the context of this discussion (each item is linked to the JQL reference if you want you want to dive deeper!).
- Field – Fields are different types of information in the system. JIRA fields include priority, fixVersion, issue type, etc.
- Operator – Operators are the heart of the query. They relate the field to the value. Common operators include equals (=), not equals (!=), less than (<), etc.
- Value – Values are the actual data in the query. They are usually the item for which we are looking.
- Keyword – Keywords are specific words in the language that have special meaning. In this post we will be focused on AND and OR.
If we go back to our example of three Acme cookies, we are essentially asking three questions to the store clerk:
- What items in your store are made by the company Acme?
- AND what items in your store are cookies?
- AND what products in your store come in packages of three?
Visually we can represent this query using the following Venn diagram. The area of interest to us is the shaded area in the center that involves all three circles.
In the first part of the example, we’re using company as the field, equals as the operator, and Acme as the value we’re querying on. If we wanted to ask the entire question to the store clerk in a structured way we could say:
company = acme AND itemtype="cookie" AND packagequantity = 3
In the above example, the query states that the company must be Acme, the item must be a cookie, and the quantity needs to be three. Each condition is related to the others by the keyword “AND.” AND means that the conditions to the left and right of AND have to be met. Since we want items that meet all three criteria, we use the keyword AND. You can also use OR, which means that only one of the conditions needs to be met to the left or right of the operator. For example, if you want to save money by looking for items on sale or store brand items, we can use this query.
Using an AND would give only the area covered by both circles. An OR returns items in either circle. I almost exclusively use AND, but there are a few cases where OR has been useful. When looking at the results of a query, using AND will return less results and using an OR will return more results.
So can we get back to JIRA?
Absolutely! Effective management of dynamic projects requires specific metrics from the issue tracker relevant to your project. JIRA offers a number of fields for you to query. Click the fields link to see what types of data you can mine from JIRA. Let’s start with a simple example.
What bugs did John Smith file yesterday in the project Pipeline?
reporter = jsmith AND project = Pipeline
In this example the person filing the bug is stored in the reporter field. JIRA also has a project field that matches the issue key. For example, issue PIPELINE-2 is in the PIPELINE project. We can set up two conditions joined by the AND keyword.
Often times you may want to have the query reference a set of items. Let’s go with a slightly more involved example.
What issues are blocking or critical in Projects A, B, and C?
priority in (Blocker, Critical) AND project in (ProjA, ProjB, ProjC)
The “in” keyword will include any item that matches any item in the list. In the above example it will return all of the blocker and critical bugs in projects A, B, and C.
This is extremely helpful for organizations that have service level agreements (SLA) with their customer base. A JQL query can easily find the issues that are not meeting that SLA.
What issues are unassigned and have not been updated in the last day?
assignee is EMPTY and created < -1d
In this query the “is empty” statement only includes issues where the value of the assignee field is blank. This query also shows how JIRA supports relative dates. The value -1d evaluates to 1 day behind the current date when the query is run. As a result, the above query will return all issues that do not have an assignee that are at least one day old.
Entering JQL in JIRA
Don’t be intimidated by JIRA’s Advanced Searching! JIRA’s JQL editor makes it easy to learn JQL. The editor uses code that hints as you type. For new users, this will greatly simplify the learning curve to get started. The JQL editor is contextually aware of the query as you type and gives real time feedback. For example, if we want to type the following JQL:
project = Pipeline
JIRA gives suggestions at each step of the way for each of the three components of our query. When you see the value you’d like to select, you can use the arrow keys or mouse to select it.
Once the query is complete, JIRA validates the JQL and lets us know the syntax is correct by the green check in the left and side of the text entry box.
Note the syntax help link which opens a reference page to the JQL language. What’s next? This is the first of a four part series aimed at building JQL Ninjas for JIRA. In future blog posts we will be focusing on:
- JQL Functions and Searching Change History
- Places in JIRA to use JQL
- Extensions in the marketplace that extend the JQL language