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.

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 jira query examples 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.

Practical Examples of Advanced JQL Queries

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 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.

Entering JQL in JIRA

I use this approach in the following example to join the Book with the Publisher entities. The FROM clause defines from which entities the data gets selected. Hibernate, or any other JPA implementation, maps the entities to the according database tables.

For example, you can run this command in the terminal on the Macintosh or Linux workstation. (Windows users will need to get curl.) If we want to grab all of the duplicate issues from the ANERDS project we could simply call the search REST endpoint. Do thought experiments & try to guess what a Scrum master might ask from you as opposed to a project manager who is managing 3 different projects. This list does not end here, but it should definitely give you an idea into the depth of function availability for advanced search in Jira.

What do You Need to Know about JQL?

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.

• Then you probably will have stumbled upon the search function in Jira.
• Be it while looking for a specific ticket, to automate workflows or to create reports.
• And the good thing is that it takes all its structure from the Jira JQL.
• Development managers often have to find areas of risk in their programs.
• Query results can be saved and used as filters and views across Jira (including boards).

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 aren’t meeting the SLA. Highlighted toggle lets you switch between the Basic & JQL (aka advanced) search.Basic search itself lets you choose the fields & corresponding values that you want to search for.

You don’t have to be technical to use JQL

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.

You can even create custom software that runs SQL queries to retrieve data and presents the information in intuitive ways. For example, 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. Visit the Jira product guide to learn more about JQL, advanced search with use cases, and more. Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause. When you choose an inheritance strategy that supports polymorphic queries, your query selects all instances of the specified class and its subclasses. With the model in the example for this blog post, you can, for example, select all Publication entities, which are either Book or BlogPost entities.

Jira Work Management Support

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.

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.

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.