Well, as it turns out – the above logic also applies to the popular project management tool, Atlassian Jira. There are some Jira instances out there with hundreds of thousands of issues/tickets tracked in them. Subqueries can return one or multiple records and can use the aliases defined in the outer query. The following example shows a query that uses a subquery to count all Books written by an Author and returns only the Authors who’ve written more than 1 Book.
- Then the JQL is populated with the records, one per returned issue id along with the Issue Lucene document in the JQ.ISSUE column.
- Plus, using SQL to read a Jira database is a bloody nightmare, and is the worst possible way to report on Jira, you really should forget it and use Jira properly.
- In the fast-paced world of project management, staying organized and effectively tracking tasks and projects is crucial for success.
- Subqueries can return one or multiple records and can use the aliases defined in the outer query.
I use this approach in the following example to join the Book with the Publisher entities. The FROM clause defines from which entities jira query examples the data gets selected. Hibernate, or any other JPA implementation, maps the entities to the according database tables.
Operators in Jira Query Language
The three flavors of search in Jira — quick, basic, and advanced — can help you find important information about your projects. The result of the above sentence is a text string that will be executed and resolved by the JQL table returning all the issues in that project version. The query controls for priority and limiting the created time properly scopes the query. We use the DESC keyword to sort in reverse (newest to oldest) so we focus our attention on the most important issues first. Functions are powerful because they can react dynamically as the environment changes.
You can also use OR, which means that only one of the conditions needs to be met. For example, if you want to save money by looking for items on sale or store brand items, we can use this query. We noted the convenience of natural language query builders while acknowledging the limitations and ongoing need for human oversight. Even as AI evolves, understanding and verifying JQL queries will remain a valuable skill, especially in complex and highly regulated environments. Our verdict – AI or not, the need to learn Jira Query Language will remain in demand. If not for building complex queries but for verifying them, when generated through AI.
Fields in Jira Query Language
That is, typing status WAS IN (‘Resolved’, ‘Closed’) is the same as typing status WAS “Resolved” OR status WAS “Closed”. This operator can be used with the Assignee, Fix Version, Priority, Reporter, Resolution, and Status fields only. 4) Function – Retrieves only the true results specified within the function and clause. SQL+JQL is a H2 in-memory database which uses the H2 API to plug tables. JQL and SQL are incompatible, not the same thing at all, so there is no “convert”. Plus, using SQL to read a Jira database is a bloody nightmare, and is the worst possible way to report on Jira, you really should forget it and use Jira properly.
The following query selects all Author entities from the database in the ascending order of their lastName attributes. All Authors with the same lastName are returned in descending order of their firstName. When you use aggregate functions, like count(), in your SELECT clause, you need to reference all entity attributes that are not part of the function in the GROUP BY clause.
Grouping – The GROUP BY and HAVING clause
This is really very useful for any new Jira users looking for learning JQL search capabilities in Jira. Find issues whose summary or description contain any of the listed words in a test project. Let’s look at another example examining the incoming bugs for our project. We want to see any new critical or blocking bugs that have come in recently to see if recent checkins have decreased stability. Often times you may want to have the query reference a set of items.
If you want to select data from more than one entity, e.g., all authors and the books they’ve written, you have to join the entities in the FROM clause. The easiest way to do that is to use the defined associations of an entity like in the following code snippet. JIRA has a stable REST API to access issues that lets you use the same JQL you do in the UI. The REST endpoint for search returns structured JSON data that’s easy to process and manipulate. Tips and tricks section covered saving filters, sharing queries, and scheduling subscriptions. These features help you get more out of the tool than to write the same queries repetitively.
Functions in Jira Query Language
Note that not all fields are compatible with this operator; see the individual field reference for details. If still navigating Jira using JQL feels daunting, there is a solution within your reach. Explore the realm of the project management course offered by Skillcubator. With our expert-led instructions, you can master project management using Jira software. For example, as you search with an assignee, issues allocated to that particular assignee appear on the board.
If you’re unfamiliar with Jira software, you can check out this video. It has insights that beginners need to know on how to use Jira for project management & activity tracking. I wanted to create a advanced search but at the end I fail everytime at the JQL. H2 is a high-performance database, however, SQL+JQL resolves all the queries against the Jira Java API, checking users’ permissions, etc.
Operators
To meet user expectations, Atlassian Marketplace offers some additional apps with JQL functions. With ScriptRunner you can also not only use more JQL values but create your own as well. Actonic has also developed an app called Power JQL, with additional JQL functions to be used for the extended search. When searching JIRA, it’s easy to perform searches that return too many issues. In the above example both diagrams have the same number of issues we care about (issues with A on them) but the first query isn’t specific enough.
Atlassian Jira is meant to be used as a collaborative project management tool. Know that advanced JQL queries will save you tons of time & improve your ITSM KPIs. We will start with the basic concepts & then debate about utility of JQL in the age of AI, along the way covering some advanced topics. And yes, we’ve included a Jira JQL cheat sheet for handy reference.
Queries can be constructed using a combination of keywords, operators, fields, and values. These well-structured queries can be used to search for issues under specific categories, like, project name, issue type, status, assignee, priority, and more. To recap, in this guide you’ve learnt the nuts & bolts of Jira Query Language, a powerful tool for advanced searching in Atlassian Jira.
