Spreadsheets are a great way to store information until they become unwieldy. When you have hundreds of columns and tens of thousands of rows, it’s much easier to use a database to store information and use Structured Query Language (SQL) to access the data. You can even create custom software that runs SQL queries to retrieve data and presents it in intuitive ways.
Let’s take a look at how Jira Query Language, or JQL, can help solve the same problem with Jira Issues, which can similarly become unwieldy in large projects.Jira Query Language can help you easily find the Jira issues that you need to make decisions without having to search through thousands of records by hand. Click To Tweet
What is JQL?
Jira Query Language, or JQL, is a powerful way to search issues in Jira. While basic search provides high-level access to issues, advanced search uses JQL to create queries to form more complex questions. Developers, test engineers, project managers and even business users can use the advanced search with JQL to find the need-to-know information.
JQL Cheat Sheet – Source: Atlassian
Some of the most popular JQL add ons include:
- JQL Tricks Plugin: Over 50 extra functions that you can use to do everything from grouping to checking for commits, pull requests or failings builds.
- JQL Search Extensions: New keywords to find issues, comments, subtasks, versions, epics, links and attachments.
- Scripted JQL Functions: An easier way to create JQL functions without having to deal with the Atlassian SDK.
Building JQL Queries
Jira Query Language enables anyone to build queries that can be executed in Jira to produce a set of results. These queries are very similar to SQL, GraphQL and other query languages that enable easy access to large databases or data sets.
Each query has a few different components:
- Fields: Fields are columns that contain different types of information in the system. For instance, `issue type` and `priority` are two default fields in Jira.
- Values: Values are the actual data in the field that the user is looking for. For instance, `bug` might be one of the possible values for an `issue type`.
- Operators: Operators are the logic that connect the field and value to produce a result. For instance, the equals (=) operator can be used to narrow down issues where issue type is `equal to` `bug` and only show those in the results.
- Keywords: Keywords are operator-like words that string together multiple parts of a query. For instance, `AND` and `OR` are the two most common keywords that can string together two sets of field-value-operator combinations. The `CHANGED FROM` … `TO` keywords and the `DURING` keyword also let you query by `time` and `status`.
JQL functions make it easy to re-use complex queries without writing them from scratch. For example, the `membersof()` function lets you query for all issues assigned to members of a particular group. Many of the JQL plugins in the Atlassian Marketplace provide access to helpful functions that you can use to simplify queries.
There are two techniques that you can use to narrow down results:
- Scope: Scoping involves narrowing down the amount of data returned by introducing qualifiers. Rather than looking for a project with the `status` of `open`, you might specify that issues should be part of the `current sprint`.
- Sort: Sorting involves ordering the data so that the most important data is listed first. For example, you can use the `ORDER BY` keyword to order the results by `priority`, `assignee` or other relevant attributes of the `issues`.
Sharing JQL Reports
Developers may need to share information with product managers (or vice versa) and product managers may need to share information with stakeholders. In both scenarios, you may want to share the results of a JQL query with others or even configure a regular email alert to send containing the relevant issues (e.g. those that need attention).
Jira Filters – Source: Atlassian
You can save queries that you build using filters—just click “Save as” to save the filter after running the JQL query. Once it’s saved, you can use the “share” button to copy a shareable link to your filter and send it to anyone in your organization. You can also use subscriptions to share filters via email with anyone regularly (e.g. stakeholders or product managers).
The Bottom Line
The Jira Query Language, or JQL, is a convenient way to access Jira issues and find the information that you need. While it can be a little intimidating at the start, learning JQL can save a lot of time in the long run—especially if you configure filters and subscriptions to automate the sending of critical queries to people regularly.
If you are incorporating Jira issues into a custom or third-party application, you can also use Jira’s REST API, which lets you use JQL queries to retrieve data in a JSON format. A common example would be creating a custom dashboard to display information for stakeholders without requiring them to log in to Jira to sort through issues by hand.
Automated release notes using power of JQL – Source: Amoeboids
For instance, Automated Release Notes for Jira one of our popular plugins pulls relevant Jira issues based on JQL to compile release notes automatically. Even the Roadmap Portal for Jira Service Desk add on is based on JQL which helps gather feedback and votes from your Jira service desk customers.