The initial part of this guide was focused on the basic concepts. That’s where we explored fields, operators, values, keywords, and functions, the building blocks of JQL queries. We also discussed the advantages of advanced search over basic search, highlighting its precision and flexibility. With the help of JQL (Jira Query Language) the search can be set up effectively. This is essential for developers, but project managers, team leads and other users might benefit from knowledge in JQL as well. So it’s safe to say that ideally every team working with Jira is able to use the advanced search with JQL.
It is, therefore, a good practice to activate the logging of the SQL statements during development to check the generated SQL statements. That is, it is used to search for issues where the specified field has a value. That is, it is used to search for issues where the specified field has no value. Mastering JQL is the key that unlocks the hidden vaults of productivity and efficiency within Jira, propelling your project management endeavors to unprecedented heights.
JQL: the most flexible way to search Jira
This will do two things – it will be easier for you to build the query in small pieces & it will also control the order of operations. Here’s a handy tip – use parentheses to group related elements together. Give your filter a meaningful name so you can easily identify it later. The most simple search feature in Jira is the search box at the top right of your screen.
- As an Atlassian partner we’ve done so in the past with multiple customers through JQL consultations or training sessions.
- The “IN” operator is used to search for issues where the value of the specified field is one of multiple specified values.
- The projection of your query defines which information you want to retrieve from the database.
- The “WAS” operator is used to find issues that currently have or previously had the specified value for the specified field.
- As you can see, I use the ‘.’ operator to navigate via the publisher attribute of the Book entity b to the related Publisher entities.
The identification variable is often called alias and is similar to a variable in your Java code. It is used in all other parts of the query to reference this entity. 5) Keyword – Words/phrases that add specific conditions to your queries. And, or, not, empty, null, order by, is and more can be counted as keywords. JQL is widely adopted by project managers, software development teams, support teams, and other Jira users.
You don’t have to be technical to use JQL
They have made this SQL like query language as popular as it is today. That’s the primary role of keywords in the context of Jira query language. For example, the default priority field has possible values of Blocker, Critical, Major, Minor, Trivial. Similarly, assignee field will have the users as possible values. 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.
Or you can select a specific subtype of a Publication, like a BlogPost. You can read more about this workaround and Hibernates proprietary support for JOINs of unrelated entities in How to join unrelated entities with JPA and Hibernate. Using “WAS NOT IN” is equivalent to using multiple WAS_NOT statements, but is shorter and more convenient. That is, typing status WAS NOT IN (“Resolved”,”In Progress”) is the same as typing status WAS NOT “Resolved” AND status WAS NOT “In Progress”. Using “WAS IN” is equivalent to using multiple WAS statements, but is shorter and more convenient.
Entering JQL in JIRA
This
will cause the resultset to return
null when it looks for the value for
“ID”. JPQL supports a set of basic operators to define comparison expressions. Most of them are identical to the comparison operators supported by SQL, and you can combine them with the logical operators AND, OR and NOT into more complex expressions. The next important part of a JPQL query is the WHERE clause which you can use to restrict the selected entities to the ones you need for your use case. The syntax is very similar to SQL, but JPQL supports only a small subset of the SQL features. If you need more sophisticated features for your query, you can use a native SQL query.
Here are some practical tips to help you enhance your Jira advanced search abilities. Simple, check the entire query below & notice how we have added the OR keyword, used brackets to combine two seemingly different criteria together. The list of JQL keywords is actually small & pretty much self-explanatory. Following keywords are available to use in Jira – AND, OR, NOT, EMPTY, NULL, ORDER BY. Since the purpose of JQL queries is to retrieve information from Jira based on advanced user needs, it needs to follow a certain pattern or structure. This detailed guide about JQL or Jira Query Language will set you on the path to success with sufficient breadth & depth of knowledge.
Use advanced search with Jira Query Language (JQL)
Using similar code and a native query casting to a Customer won’t work, a ClassCastException will be raised.
Constructor references are a good projection for read-only use cases. They’re more comfortable to use than scalar value projections and avoid the overhead of managed entities. As you can see, I use the ‘.’ operator to navigate via the publisher attribute of the Book entity b to the related Publisher entities. That creates an implicit join between the Book and Publisher entity which will be translated into an additional join statement in the SQL query. Path expressions create implicit joins and are one of the benefits provided by the entity model.
For example, if you wanted to see all the critical bugs created in the last 30 days from project AR that are not fixed – this is what the basic search would look like. JPQL is a powerful query language that allows you to define database queries based on your entity model. But there is an important difference that I want to point out before I walk you through jira query examples the different parts of a JPQL query. In all the queries we’ve discussed so far, the data is reflective of the current time the query was run. But values for fields like assignee, priority, fix version, and status often change over the course of an issue’s lifecycle. JIRA stores each update an issue goes through, known as an issue’s change history.
Having a JQL cheat sheet can be incredibly helpful for quick references. Download our comprehensive PDF cheat sheet below and keep it handy. It includes the most commonly used functions, operators, and syntax examples, making it easier for you to construct queries without having to memorize everything. Hibernate uses the mapping information of the selected entities to determine the database columns it has to retrieve from the database.
Make Jira do as much work as possible
Development managers often have to find areas of risk in their programs. Good queries often take a bit of refinement as they get developed. In this query the “is empty” statement tells JIRA to return only issues where the value of the assignee field is blank. 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 and haven’t been updated in the past day. By implementing these tips and tricks, you can make the most of Jira advanced search and significantly improve your ability to manage and track issues in Jira.
