The Ultimate JQL Cheat Sheet: How To Get Started, from an Expert

First of all, we’re calling it JQL.

But read that however you like. There’s a divide between the Jira admins and project managers of the world (“J Q L”) and the developers who use Jira (“J-quill” in the style of SQL or “sequel.”) Atlassian themselves use the acronym, though that quickly lands us in .gif territory.

Call it what you want, we can agree that JQL is a powerful tool that helps Jira users access information faster and easier than manual methods allow, especially when you’re working at scale. Sadly, the programming feel of the language can scare off less technical Jira users.

I’m here to help you with that.

My name is Patrick Shanley, I’m the Head of Engineering here at Visor. When our Product Management team has brilliant ideas for our app, I create the best plan of attack for bringing those ideas to life. Unsurprisingly, I spend a lot of time in Jira, and I use a lot of JQL.

Visor is a project portfolio management tool that makes Jira roadmaps, dashboards, and reports. Our JQL importer pulls in multiple Jira projects, allowing teams to create portfolio views they can share with anyone.

Because so many of our users rely on JQL, I encounter a lot of people out there who struggle how to use JQL – even when they’re frequent Jira users. Hopefully this guide will help demystify some of that and make JQL feel more accessible for everyone.

What is JQL?

Let’s start with the technical. J Q L stands for Jira Query Language. Essentially, JQL is what’s called a query language: a specific way of writing asks to get the most flexible search possible from a database.

It can be very powerful, but it also can be intimidating and also sometimes a little bit esoteric if you’re not familiar with the ins and outs of how it works. It can also be a bit limiting if you don’t feel confident in exploring everything it can do.

JQL is much easier to use once you’re comfortable with it and aware of the use cases where it can help you the most. That’s when JQL truly opens up.

When is JQL useful?

JQL’s primary usage is that it serves as a very advanced way of filtering Jira issues in large-scale projects and when you’re working with big instances.

JQL Use Case #1: Advanced Filtering Across Your Jira Instance

Say you needed to know all of the in-progress tickets across all your projects that were due in the next few days. Most tickets in your Jira instance have a status called Implementing — except for one project that uses a different status setting than the rest; that Jira project calls it In Progress. To complicate things further, yet another Jira project might call that same field Doing. And they all mean the same thing, but because different projects use different terms, it’s tricky to find them all at once.

This is where JQL can help you!

You can write a JQL request where you say “status is:” and then type in a list of different ways the same statuses are phrased. Then you include the terms “due date is less than five days from now”.

By taking the time to write that out, you’ll then get a list of all of those projects, all those tasks that fit the criteria that you just laid out.

JQL Use Case #2: Create Portfolio Roadmaps, Reports & Dashboards

JQL is best when you’re trying to ask a question across projects, but it’s not limited to that.

In fact, Visor (which integrates with Jira) offers a JQL importer so that users can bring in multiple Jira projects, epics, or even initiatives into one roadmap or report. While Jira offers built-in roadmaps, Timeline and Advanced Roadmaps, only Advanced Roadmaps offers portfolio roadmaps. (And that’s only available on a Jira Premium or Enterprise plan).

With the use of JQL, you can achieve Advanced Roadmaps capabilities in Visor for way less money.

For instance, in the JQL example you see below, you can list multiple projects to pull in more data across your portfolio of projects. You can even add what type of Jira issue hierarchy you want to pull in, when the issue was created and its status.

Sample query: 
project in ("Project Mercury", "PTC") and type in ("Epic", "Task") and status = "To Do" and created >= -180d

Visor’s JQL Importer, often used for project portfolio management:

If you use JQL to pull in multiple Jira projects, epics, or initiatives in apps like Visor, you can then easily create roadmaps, dashboards, Table views, and capacity-planning charts across their portfolio of projects.

Often, stakeholders (or even clients) want to get the big picture. While Jira Gantt charts are great for more granular updates for your team, other audiences typically want to see things from a 30,000-foot-view.

Visor Roadmap with Jira data pulled in with JQL:

JQL Use Case #3: Save Time With Saved Filters

One of the other awesome things about JQL is that if you find yourself using a particular query frequently, you can save it to Jira and turn it into a report for yourself.

If you wanted to limit a report to a specific project, but you wanted to have some information to show your stakeholders the status of a roadmap, you can write the JQL and then save it as a filter. And that saved JQL can then drive a report inside of Jira.

These reports are even useful if you have a few non-technical team members who don’t care about all the details of everything inside of the issue and want to focus on progress. Whip up a custom report using JQL and they won’t need to have a deep understanding of Jira to see that progress.

JQL Use Case #4: Avoid Manually Scanning Data

If I’m working within one project, I can probably just go to the view that Jira gives me and manually scan to see all issues in a board view or in a list view. I could go into Visor’s PTC project and I could see the board and visually see what’s being worked on, what’s done, and what we haven’t started yet.

However, if I was managing a few different teams (or if I had different kinds of departments all working in Jira), and I’m the critical person who needs to understand all of these moving pieces at once, it would take way too long to manually go look at each project to get the information I need.

If I want to truly understand the whole system and ask questions or find items across projects, I’d use JQL to achieve that. I could view this backlog dataset in Jira’s List view. Alternatively, I could look at it in a Jira spreadsheet made with Visor.

Jira Spreadsheet using Visor’s Table view:

JQL is infinitely valuable if you’re trying to select a set of issues for whatever purpose. Maybe you just want to go through your backlog and select all backlog tickets that have a certain label associated with them, JQL can give you that list. Now you can go through and spot where you’ve already handled an issue, where you might have duplicates, where you need to prioritize a task, or where an issue is no longer a problem. It’s amazing for clearing out your backlog in a hurry.

Need more help with using JQL? We’ve got you!

The Components of a JQL Command

In the next section, you’ll see some some common queries that you’ll encounter in Jira. But first, let’s go over the various components that make up a query.

Sample JQL command in white font; corresponding components in yellow:

Field

As you can see above, fields are the Jira fields you’re pulling in the query based on the values you set. You can also pull Jira projects and custom fields.

Operator

Operators can be words or symbols. Either way, they decide how you will compare fields on the left of an operator to the field (or fields, if it’s a function) to the right.

Keywords

Keywords typically combine two or more sets of commands (or clauses) together. Some keywords might also change the logic of the clauses (e.g., empty or null keywords).

Functions

Functions replace values that you’d otherwise regularly have to pull manually via a JQL command. In the example above, the function is finding issuetypes that were created at least 180 days ago. This function allows you to pull the fields you need that were created 180 (or more) days ago–no matter what day you’re running the JQL command.

Common JQL Fields, Operators, Keywords & Functions:

JQL Examples (with Explanations)

Sometimes it’s easier to edit other JQL commands to understand their syntax. You can start writing your own from scratch once you have your footing with this powerful query language.

Below you can find give different commands with the laymen’s explanation above each.

Example 1: Pull in all epics from numerous Jira projects

project in ("PTC", "BLG", "FIG") and issuetype in ("Epic", "Project)

Example 2: Pull issues in a project that have the status “in progress” or “to do”

(project = "AG") AND (statusCategory="To Do" OR statusCategory="In Progress")

Example 3: Pull in issues from a project with a specific label; order them

project = "PTE" AND labels = marketing-qe-day order by Rank DESC

Example 4: Pull Epics and Tasks from a project that have a “To Do” Status & were created 180 (or more) days ago

(project = "Project Mercury") and issuetype in ("Epic", "Task") and status = "To Do" and created >- 180d

Example 5: Find all tickets that were resolved exactly three months ago

resolutiondate >= startOfDay(-3M) AND resolutiondate < endOfDay(-3M)

How to start using JQL

If you’re itching to get started, I highly recommend you begin with Atlassian’s Jira and JQL documentation. They’re extremely comprehensive and helpful for a beginner. (And now that you’ve got this cheat sheet letting you know when and how you might want to use JQL, you’ll be able to follow along step by step as they explain how to use it!)

If you’re looking to just jump right in, there’s two different ways to start.

The first and easier way is to follow Jira’s basic JQL Select, which is a dropdown menu you’ll see when you navigate to the JQL bar, right at the “View all issues” button at the bottom of the search bar menu.

From here, you can choose your own filters as you click through your Jira fields and values.

It’s a little bit limited, but it sets you up to understand what the different options for search, or keys, can be.

When I say keys, I mean the values as they exist on an issue are all keyed into a name. At the time you created it, there’s a date time that gets stamped every time you make an issue. That’s the “Created” key. So once you know that the created timestamp is called created by exploring it in JQL, you can add that to your request to specify things like issues created before or after certain dates. 

Jira is full of terms like that with all of these keys, which you won’t know without exploring JQL.

The other way to jump in is by typing directly into the JQL bar. When you start typing, Jira will give you options to fill in. I suggest playing with those to see what they do before trying to get a more exact search going.

JQL troubleshooting

JQL can be very powerful, but there are a few things it doesn’t do very well.

For instance, if you want to search for “child” issues, JQL won’t be much help. If I wanted to pull all the issues for a given project, JQL could show me the epics. But there’s really no great way in JQL to be like for all of the projects that I selected and also bring in the tasks and then from all of those tasks also bring in those sub-tasks. In order to do that, if you really needed to, you would need to write out three separate JQL statements.

This is where the Atlassian Marketplace comes in. People have created functions and mix-ins that will help achieve that for you.

If you’re curious how those work, it will basically run another JQL statement inside of yours, and it may do that several times in order to get the detail you want. It says “Ok you want those results. Now you want these results from those results. Now you want these results from those results from the first results!” While you’d be executing three different JL statements, it would return one set of issues.

There are lots of programs in the Atlassian Marketplace that do this, and I’ve recently created one for Visor – so you can expect to see that in our app soon.

The other pitfall I see people running into with JQL usually comes up if they’ve never used any programming or query languages before, so they don’t have a complete grasp of the different values.

If I went to the JQL bar and typed in “cheeseburger = hello” it’s not going to do anything because Jira doesn’t know what that means.

Obviously this is a silly example, but realistically this comes up if you don’t know the correct name for a particular field. If you’re searching for a status but the field you want is actually called status category, you’re likely to get the wrong results.

If you’re working within Jira, you’ll usually see an error message if you ask JQL for something it can’t find. In Visor, we’ve taken the time to give you all of these error messages. So if you’re searching for something within Visor and it runs into an error from Jira, Visor will let you know that with as much detail as Jira would. That’s thanks to our robust two-way integration.

What’s next for JQL

You’ll notice that, when you’re exploring the JQL bar, you’ll see a button on the left that says AI. This is one of Jira’s newest features as Atlassian explores incorporating more AI for project management.

You can ask the AI to help you in finding what you need, which is especially helpful if you’re new to JQL and don’t feel fully confident in handling it all yourself yet.

Say I wanted to find all issues in progress, like in our first example. I could ask the AI for what I needed just like I’d ask a person, in regular language rather than a programming language. It will convert that ask to a JQL request.

You’ll notice in this example it’s a little less robust than our manual JQL request. The AI doesn’t know what you know: that different projects call the “in progress” status different things. This is where you’ll want to iterate and explore a few times by varying your request until you get the result you want.

It’s early-stage still, but this AI could end up being extremely powerful one day!

JQL Cheat Sheet PDF and Parting Tips

If you’re intimidated by JQL… don’t be. Because JQL is read-only, you truly can’t mess anything up. More than that, the trial and error method is the best way to proceed when exploring JQL for the first time.

Take this as your challenge: Download our free JQL Cheat Sheet PDF and then mess with JQL for ten minutes. Just those ten minutes will set you up for success. Later on you can build on that knowledge and try new things, you’ll only ever get better at it. 

Free JQL Cheat Sheet PDF:

Anybody can be a JQL expert by spending a little bit of time exploring.

If you need better ways to pull reports and get everyone on our team on the same page, no matter how many different Jira projects you have, Visor can help you build nuanced, detailed views for all your Jira data. Try it for free.

If this article was helpful, consider reading a few more:

1. Project Success: The Art of Delegation in Project Management
2. Jira For Non-Software Projects: How To Get The Best Results

Patrick Shanley

Patrick Shanley

Head of Engineering

Visor's Head of Engineering, pushing toward delivering a best-in-class product to change the way people work and interact when their tools fall short.

New Jersey