Power of JQL for complete reporting

Don't get confused you who knows Java, I'm not going to talk about Java query language. This article shall describe JQL as native and most flexible way of searching for issues in JIRA. The most visible benefit of this is its openness towards non-technical people. So not only developers, testers but as well managers and business people have a chance to do quick and very effective search on their own to see the current progress inside the project. Shall you have some experience with SQL queries then you may find JQL very easy to use even without reading a single how-to article. First of all it comes with code hints and real-time validation to give instant feedback over the syntax of your query. What do you actually need to know before you start using it? Clearly almost nothing but yet it is good to know at least the parts or say structure the query uses. So let's forget about simple search which is sufficient enough for obviously simple queries but sometimes it looks a bit limiting when it comes to more complex query.

Back to the structure, each query has three basic parts: fields; operators and values. You also optionally link them together with few given keywords.

• Fields - different types of fields or information registered in the system. Some of system fields are priority, issue type, status, resolution etc.
• Operators - this is no doubt the major aspect of the query, using them you express the relation between fields and values. The most used will be equals (=), not equals (!=), 'in' 'not in' and even a bit vague contains (~). This list is not exhaustive but that is not my point here.
• Value - well, this is what you are actually looking for, the data the name basically the value of the field you are interested in.

Keywords - despite being optional without using keywords you may as well be using simple query. Typical examples are surely AND, OR, EMPTY, ORDER BY etc.

Let's get started with few basic examples

What defects were filed by specific person starting from the oldest one?

project = "YOURPROJECT" AND issuetype = Defect AND reporter = YOURUSER ORDER BY createdDate ASC

What defects with high priority are still not resolved?

project = " YOURPROJECT " AND issuetype = Defect AND Priority=High AND status != resolved

What defects which are still open are not updated longer than one day?

project = "Smart Message" AND issuetype=defect AND status=open AND updated <= -1d

I can see a smile on your face well or a grim depends on your project role J reason being you can imagine that anyone with access to JIRA project can construct with very little effort reports to give instant and very clear picture over project progress. Also remember that you can save your query to be able to reuse it later.

By now you should have quite clear idea how to construct queries and frankly you can as well live without knowing something more. Pity but still an option. JQL still offers more to explore for example functions.

Functions – there is quite a massive list of functions JQL have which they sort of pack a complex logic in easily reachable and usable manner for end user. In a clause function is preceded by an operator which is, we know by now, preceded by a field. To give an example of real usage keep in mind that unless explicitly specified in the query JQL doesn’t return empty fields in result. So for example if you want to search for unassigned issues you would enter assignee != currentUser() OR assignee is EMPTY. Or how about this, what if you want to see all the issues which got closed during last release? Year right fixversion= "YOURRELEASE" that is correct… but what if you want to make this query more generic so that you don’t have to re-develop it for next use? Function is the answer

project = "YOURPROJECT" AND issuetype=defect AND status in (resolved, closed) and fixVersion = latestReleasedVersion()

Nice! However what I like the most is this. Related to issue’s lifecycle there is one specific attribute which comes very handy. History – why is it important? Well we can see how to project changed over the time, so for example we want to see if the team follows the correct lifecycle, or you want to see how many issues have been resolved by specific person simply anything which has changed over the course of time.

project = "YOURPROJECT" AND issuetype=defect AND status CHANGED FROM "In Progress" TO "Resolved" BY dkopp

Or another one, you just created an issue, gone away and forgot the issue key?

project = "YOURPROJECT" and created >= startOfDay() ORDER BY Key DESC

and many more…

At this point you are able to create advanced search queries using JQL which they cannot be defined using basic search (functions or ORDER BY clause) but how about reusability? Yes all the queries you create you can save them, when you save them they become filters. Such filters can be shared with anyone provided you mark your filter as public. You can as well favorite filters you are interested in, you can clone then and update, define filter specific column order or even subscribe to filters.

More? Sure! JIRA comes with REST API to access issues exactly the same way as you would do in UI. It may be more technical however it really uses JQL same as you do in UI, just to repeat. This may be handy if you access JIRA from any of your application and want to present data in a form of dashboard. Simple example, you want to see all open issues in your project no matter what issue type.

https://YOURSERVERNAME.com/rest/api/2/search?jql=project=YOURPROJECT%20and%20status=open

The result will be JSON which you can use then to process in your application…

There is certainly way more you can do with JQL but hopefully you got an idea now how to use it.

Happy searching, happy reporting, happy exploring! Punchline