Data is one of the most valuable resources in the world right now, but the way it is stored and accessed can impact its efficiency and usability. Spreadsheets are a great way to store information until they become unwieldy, and even a basic lookup on such sheets might crash the system, let alone advanced search. 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 TweetWhat is JQL?
Jira Query Language, or JQL, is a powerful way to search for issues in Jira. While basic search provides high-level access to issues, advanced search in Jira 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
In addition to built-in JQL, the Atlassian Marketplace contains plugins with advanced JQL functionality. The Full-Text Search Attachments for Jira Plugin, for example, enables JQL statements to search attachments for the name, type, and content. You can even build your own plugins with Java, JavaScript, CSS, and HTML skills.
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 search in Jira for 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.
Is JQL similar to SQL?
JQL, like SQL and GraphQL, enables easy access to large data sets. These queries are similar in nature, even though they operate on different platforms. 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.
Does Jira use SQL?
Jira uses JQL. While the structure of JQL is similar to that of SQL, there are small differences. Those who have worked on SQL can understand and execute JQL queries.
Building JQL Queries
Before building a query, understanding a few of its different components is essential. Knowing what fields, Values, operators, and keywords denote will result in an efficient query.
Amoeboids-Common-JQL-QueriesEach query has a few different components:
Fields are columns that contain different types of information in the system (like issue type and priority).
Values are the actual data in the field under consideration. In the case of the ‘issue type’ field, values can be ‘bug’, ‘feature request’ etc.
Operators connect the field and value to produce a result. (= is an operator).
Keywords connect different and/or multiple parts of a query. There are many JQL plugins in the Atlassian Marketplace that can provide access to helpful functions which simplify queries.
How do I create a query in Jira?
Queries in JQL help in finding the right data, be it issues, feature requests, or something else. There are two ways to go about it – Scope and sort. Scope introduces qualifiers to whittle down the results, and sorting involves ordering the data to list the most important data first. The `ORDER BY` keyword, for example, can be used to order the results by `priority, `assignee` or other relevant attributes.
These methods can be used to perform 2 types of search:
Basic Search or simple query, which allows the search of tickets by selection parameters and entering their values, and an advanced search or complex query that makes provisions to write custom queries that have multiple parameters.
Simple queries (basic search) are a combination of fields and operators. They are also called ‘clauses’. A simple query in JQL is known as a “clause” and it consists of a field, followed by an operator, followed by one or more values or functions. Here’s an example.
These simple queries are connected using keywords or functions that lead to complex queries, depicted in the 2nd example above. While the first example shows how fields are assigned values, the second demonstrates a complex query in action where functions and keywords link clauses together, to achieve the desired outcome.
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, one of the popular plugins that we developed – Automated Release Notes for Jira 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. These tools help in automating important but routine aspects of the software development life cycle, freeing up valuable time for managers and developers.