Unlock the full potential of Jira with Jira Query Language (JQL)! This powerful tool lets you create custom queries to filter and sort issues with precision. Want to manage tasks based on due dates, statuses, labels, and more? JQL can do that!
In this blog post, we’ll dive into the world of JQL, breaking down several key queries and showing you how to use them to streamline your project management. Get ready to revolutionize your workflow and take your Jira skills to the next level!
JQL Syntax and Components
Before diving into the specific queries, let’s briefly review the basic components of JQL:
- Fields: These are the data points you want to query (e.g.,
status,duedate). - Operators: These define the relationship between the field and the value (e.g.,
=,<,<=). - Values: These are the specific data points you’re looking for (e.g.,
Done,now()). - Functions: These are built-in operations that return values or sets of values (e.g.,
endOfWeek(),startOfWeek()). - Keywords: These are used to connect different parts of the query (e.g.,
AND,OR,NOT).
Overdue Tasks
Query:
"Baseline End Date" < now() AND status not in (Withdrawn, Completed, Done) AND statusCategory != Done
Explanation: This query finds tasks that are overdue by comparing their “Baseline End Date” with the current date (now()). It excludes tasks that are in the statuses “Withdrawn”, “Completed”, “Done”, or any status categorized as “Done”.
Tasks Due This Week
Query:
"Baseline End Date" > now() AND "Baseline End Date" <= endOfWeek() AND status not in (Withdrawn, Completed, Done) AND statusCategory != Done
Explanation: This query identifies tasks due within the current week. It checks if the “Baseline End Date” is greater than the current date and less than or equal to the end of the week (endOfWeek()). It excludes tasks with specific completed statuses.
Tasks Due Next Week
Query:
"Baseline End Date" >= startOfWeek(1) AND "Baseline End Date" <= endOfWeek(1) AND status not in (Withdrawn, Completed, Done) AND statusCategory != Done
Explanation: This query finds tasks that are due in the next week. It uses startOfWeek(1) and endOfWeek(1) to specify the next week’s start and end dates.
Tasks Due Beyond Two Weeks
Query:
"Baseline End Date" >= startOfWeek(2) AND "Baseline End Date" <= endOfWeek(40) AND status not in (Withdrawn, Completed, Done) AND statusCategory != Done
Explanation: This query retrieves tasks due beyond the next two weeks, starting from the second week (startOfWeek(2)) up to the 40th week (endOfWeek(40)).
Requirements Covered
Query:
issuetype = Story AND issueFunction in hasLinkType(Tests)
Explanation: This query finds all stories that have test coverage by checking if they are linked with a “Tests” link type.
Requirements Not Covered
Query:
issuetype = Story AND issueFunction not in hasLinkType(Tests)
Explanation: This query identifies stories without test coverage, ensuring no links with the “Tests” type.
Overdue Tasks
Query:
("Baseline End Date" <= -1d OR duedate <= -1d) AND status not in (Withdrawn) AND statusCategory != Done
Explanation: This query finds overdue tasks by checking if the baseline end date or due date is in the past and excluding specific statuses.
Tasks Due in the Next 7 Days
Query:
"Baseline End Date" >= 1d AND "Baseline End Date" <= 7d AND statusCategory != Done OR duedate >= 1d AND duedate <= 7d AND statusCategory != Done AND status not in (Withdrawn)
Explanation: This query identifies tasks due in the next 7 days, considering both the baseline end date and due date, and excluding completed tasks.
Tasks Due in the Next 2 Weeks
Query:
duedate <= 14d AND duedate >= 8d
Explanation: This query retrieves tasks due in the next two weeks, from the 8th to the 14th day from today.
Tasks Due Beyond Two Weeks
Query:
duedate <= endOfWeek(365d) AND duedate >= startOfWeek(15d) AND status not in (Withdrawn) AND statusCategory != Done
Explanation: This query finds tasks due beyond two weeks, starting from the 15th day and going up to one year (endOfWeek(365d)), excluding completed tasks.
Tasks with No Due Dates
Query:
"Baseline End Date" is EMPTY AND duedate is EMPTY
Explanation: This query identifies tasks that do not have a baseline end date or a due date.
Open RAID per Assignee that Block or Is Blocked By FSD/TSD
Query:
project = CON AND issuetype in ("Action Item", Risk, Issue, Decision) AND status not in (Complete, Completed, Withdrawn, Closed) AND component is not EMPTY and issueLinkType in (Blocks, "is blocked by")
Explanation: This query focuses on open RAID (Risks, Actions, Issues, Decisions) items in a specific project that either block or are blocked.
Prep FSD Past Due
Query:
"Baseline End Date" < now() AND Status not in (Completed, Withdrawn, Done) AND statusCategory != Done
Explanation: This query focuses on tasks labeled “PrepFSD” that are past their due date. It ensures these tasks are not in completed statuses.
Prep FSD Due This Week
Query:
labels = PrepFSD AND "Baseline End Date" <= endOfWeek() AND "Baseline End Date" >= startOfWeek() AND "Baseline End Date" >= now() AND labels in (Conversion, Manual, Configuration) AND status not in (Completed, Withdrawn) AND statusCategory != Done
Explanation: This query targets “PrepFSD” tasks due this week, including only those with specific labels (“Conversion”, “Manual”, “Configuration”) and excluding completed tasks.
Development Due Next Week
Query:
labels = ExtractBuild AND "Baseline End Date" <= endOfWeek(1) AND "Baseline End Date" >= startOfWeek(1) AND labels in (Conversion, Manual, Configuration) AND status not in (Completed, Withdrawn) AND statusCategory != Done
Explanation: This query finds development tasks labeled “ExtractBuild” due next week. It includes tasks with specific labels and excludes completed ones.
TSD Approvals Completed Last Week
Query:
issuetype in ("Execution", Defect, "Event", "Task", Integration, Conversion, Report, "Integration Subtask", Configuration, Task) AND labels = TSDApprove AND labels in (Report, Integration, Extension, Conversion, IntegrationExtSup, Configuration, Manual, Extension-Retrofit) AND status in (Completed, Done, Complete) AND "Actual Complete Date" >= startOfWeek(-1) AND "Actual Complete Date" < startOfWeek()
Explanation: This query retrieves TSD approvals completed last week, filtering by issue types and specific labels, and checking the completion date within last week’s range.
TSD Approvals Completed This Week
Query:
project = CON AND issuetype in ("Conversion Execution", Defect, "Event", Task", Integration, Conversion, Report, "Integration Subtask", Configuration, Task) AND labels = TSDApprove AND labels in (Report, Integration, Extension, Conversion, IntegrationExtSup, Configuration, Manual, Extension-Retrofit) AND status in (Completed, Done, Complete) AND "Actual Complete Date" >= startOfWeek() AND "Actual Complete Date" <= now() AND "Actual Complete Date" <= endOfWeek()
Explanation: This query focuses on TSD approvals completed this week within a specific project (CON0003139), using similar criteria as the previous query but within the current week.
Tasks Due Until Today
Query:
duedate <= Now()
Explanation: This simple query finds all tasks due today or earlier by comparing the due date with the current date.
P2 Issues
Query:
issuetype in ("Task", defect) AND issueFunction in linkedIssuesOf("issuetype in (Report, Integration, Extension) and 'test cycle' = 'P2' and statusCategory != done") OR "test cycle" = P2
Explanation: This query identifies P2 issues, including tasks linked to P2 issues and those directly in the P2 test cycle.
Filter Linked Issues :
issuefunction in linkedissues of ("key in ( keyid) - This will filter all the linked child jira issues under the "keyid". issuefunction in linkedissues of ("key in ( keyid1, keyid2, keyid3)") OR key in (keyid1, keyid2, keyid3) - This will filter all the linked child jira issues under the "keyid1, key2, key3" and also the parent key as well. Color Fields:
if (statuscategory!= Done and baseline_end_date < today(); "{color.red} * Overdue* {color}")This will find if the particular issue is overdue and mention it in red font.
Search Jira Key Range:
issuekey >= key1 and issuekey <= key50This will display the key 1 to key 50.
Two conditions:
project=CON0003139 AND issuetype in (Report, Integration, Extension) AND issueFunction in linkedIssuesOf(“issuetype=Task AND labels=CCB-Rework-Retest”) and labels = CCB-Rework-Retest
Roles in Jira
issueFunction in memberOfRole(assignee, “P Team Member”) OR issueFunction in memberOfRole(assignee, “P Lead”) OR issueFunction in memberOfRole(assignee, “P Visitor”) OR issueFunction in memberOfRole(assignee, “Super User”)
issueFunction in memberOfRole(assignee, “Client Team Member”) OR issueFunction in memberOfRole(assignee, “Client Lead”) OR issueFunction in memberOfRole(assignee, “Client Visitor”) OR issueFunction in memberOfRole(assignee, Commenter)
Track Issues Based on Comments in Jira:
- Find issues commented on by a specific user:
issueFunction in commented("by jsmith")This query shows all issues commented on by the user “jsmith.” - Find issues commented on within a date range:
issueFunction in commented("by jsmith after -1w")Retrieves issues commented on by jsmith in the past week. - Find issues with comments containing specific text:
issueFunction in commented("text ~ 'urgent'")Returns issues where comments contain the word “urgent.” - Combine with other filters:
project = "PROJECTKEY" AND issueFunction in commented("by jsmith after startOfMonth()")Finds issues in a specific project where jsmith commented since the start of the month.
The issueFunction in commented() is a powerful tool in Jira for tracking comment activity, especially in workflows that heavily rely on comments.
Track Issues Based on User Roles:
- Find issues by role membership:
issueFunction in memberOfRole(assignee, "Client Team Member")– Finds issues where the assignee is in the “Client Team Member” role.issueFunction in memberOfRole(assignee, "Client Lead")– Finds issues where the assignee is in the “Client Lead” role.issueFunction in memberOfRole(assignee, "Client Visitor")– Finds issues where the assignee is in the “Client Visitor” role.issueFunction in memberOfRole(assignee, Commenter)– Attempts to find issues where the assignee is a member of the “Commenter” role (if this role exists).
Assigning Roles in Jira Projects:
- Access Project Settings:
Navigate to the desired project and open “Project Settings.” - Manage Project Roles:
In “Project Settings,” go to “Project Roles” to view configured roles. - Assign Users or Groups:
Select the role, then add users or groups to it. - Save Changes:
Ensure changes are saved to apply new assignments.
Track Issues with Attachments:
- Find all issues with attachments:
issueFunction in hasAttachments()This retrieves issues that have one or more attachments. - Find issues with attachments in a specific project:
project = "PROJECTKEY" AND issueFunction in hasAttachments() - Find open issues with attachments:
status = "Open" AND issueFunction in hasAttachments() - Find recently created issues with attachments:
created >= -7d AND issueFunction in hasAttachments()This shows issues created in the last 7 days with attachments.
How to fetch the issues of one type linked to another type:
project = con AND issuetype in (Task) AND isssuefunction in linkedIssuesOf("issuetype in (report,integration,extension)")The above filters all the issuetype of Task that has been linked to issue type of report, Integration &extension.
project = con AND issuetype in (Task) AND issuefunction in linkedIssuesOf("issuetype in (test)")The above filters all the issuetype of Task that has Test issue linked to it.
User Story Not linked to Test and Fix version as P3:
issuetype = Story AND issueFunction not in hasLinkType(Tests) AND fixVersion = P3
This JQL query filters issues in Jira to find all items of type “Story” that do not have any links of type “Tests” and have a fixVersion set to “P3.”
Test Issues in Project CON0003139 with Fix Version P3 Excluding Specific Labels”
This JQL query filters issues in Jira to find all items of type “Test” within the project “CON0003139” that have a fixVersion set to “P3” and do not have any of the specified labels.
project = CON0003139 AND issuetype = Test AND fixVersion = P3 AND labels not in (Integration, Conversion, Report, Extension, Standard, PUB_TC)
“Test Execution Issues with Fix Version P3 in Project CON0003139 Excluding Specific Labels”
project = CON0003139 AND issuetype = “Test Execution” AND fixVersion = P3 AND labels not in (Integration, Conversion, Report, Extension, Standard, PUB_TC)
This JQL query identifies “Test Execution” issues from the project “CON0003139” that have the fix version “P3” and exclude those with any of the labels specified (Integration, Conversion, Report, Extension, Standard, PUB_TC).
“Issues Associated with Test Execution ID 169523”
issue in testExecutionTests(“169523”)
The JQL query issue in testExecutionTests("169523") retrieves issues that are associated with the test execution having the ID “169523.” This function is useful for finding all test cases related to a specific test execution record.
“Issues Associated with Test Plan ID [Test Plan Filter ID]”
The JQL query issue in testPlanTests("Test Plan Filter ID") retrieves issues associated with the specified test plan, where “Test Plan Filter ID” should be replaced with the actual ID of the test plan. This function helps in finding all test cases related to a particular test plan.
issue in testPlanTests(“Test Plan Fitler ID”)
“Defects with Label ‘P2TestingDefect’ Linked to Test Execution Issues”
This JQL query finds issues of type “Defect” with the label “P2TestingDefect” that are linked to issues of type “Test Execution.” The issueFunction in linkedIssuesOf("issuetype = 'Test Execution'") part searches for defects related to any test execution issue.
issuetype = Defect AND labels = P2TestingDefect AND issueFunction in linkedIssuesOf(“issuetype = ‘Test Execution'”)
“Test Execution Issues Linked to Defects with Label ‘P2TestingDefect'”
This JQL query finds issues of type “Test Execution” that are linked to defects with the label “P2TestingDefect.” The issueFunction in linkedIssuesOf("issuetype = 'Defect' AND labels = P2TestingDefect") part identifies test execution issues associated with defects that match the specified criteria.
issuetype = “Test Execution” AND issueFunction in linkedIssuesOf(“issuetype = ‘Defect’ AND labels = P2TestingDefect”)
“Tests with Fix Version P3 Not Linked to Stories”
issueType = Test AND fixVersion = P3 AND issueFunction not in linkedIssuesOf(“issuetype = story”)
Explanation:
This JQL query selects issues of type “Test” with the fix version “P3” that are not linked to any issues of type “Story.” The issueFunction not in linkedIssuesOf("issuetype = story") part ensures that the returned test issues do not have links to story issues.
“Dynamic Status Labels for Initiatives and Tasks”
To achieve dynamic status labels such as Completed, Overdue, or Due this Week, you need to use separate JQL queries for each condition, as JQL itself doesn’t support conditional formatting. You can use these queries in filters, dashboards, or reporting tools to reflect the status based on your criteria.
IF(issueType = ‘Initiative’ or issuetype = ‘Task’,
IF (
status = ‘Closed’ or status = ‘Completed’; “{color:green}*Completed*{color}”;
(status != ‘Closed’ or status != ‘Completed’) and baseline_end_date < today(); “{color:red}*Overdue*{color}”;
JQL {statusCategory!= done and ‘Baseline End Date’ >=startOfWeek() and ‘Baseline End Date’ <= endOfWeek()}; “{color:orange}*Due this Week*{color}”
)
)
“Test Execution Issues in Project CON0003139 with Fix Version P3 and Status ‘To Do’, Excluding Specific Filters and Unassigned Components”
project = CON0003139 and issuetype = “Test Execution” and fixVersion = P3 and status = “To Do” and not (component is EMPTY or filter = 160916 or filter = 160917 or filter = 160918 or filter = 160919)
This JQL query retrieves “Test Execution” issues from the project “CON0003139” that have the fix version “P3” and are in the “To Do” status. Additionally, it excludes issues if they belong to any of the filters with IDs 160916, 160917, 160918, or 160919, or if they have no components assigned.
“Defects Not Linked with ‘defect of’ or ‘blocks’ Link Types”
This JQL query finds issues of type “Defect” that are not linked using the link types “defect of” or “blocks.” Essentially, it filters out defects that have links with these specified types.
issuetype = Defect and issueLinkType not in (“defect of”,blocks)
Structures JQL
Custom Fields and Labels: To display dynamic status labels (e.g., Completed, Overdue) in Jira, consider using a custom field or labels that you update manually or via automation rules based on the criteria. Jira’s native JQL cannot format or color-code issues based on conditions.
IF(issueType = ‘Initiative’ or issuetype = ‘Task’,
IF (
status = ‘Closed’ or status = ‘Completed’; “{color:green}*Completed*{color}”;
(status != ‘Closed’ or status != ‘Completed’) and baseline_end_date < today(); “{color:red}*Overdue*{color}”;
JQL {statusCategory!= done and ‘Baseline End Date’ >=startOfWeek() and ‘Baseline End Date’ <= endOfWeek()};”{color:orange}*Due this Week*{color}”;
JQL {statusCategory!= done and ‘Baseline End Date’ >=startOfWeek(1) and ‘Baseline End Date’ <= endOfWeek(1)};”{color:blue}*Due Next Week*{color}”;
JQL {statusCategory!= done and ‘Baseline End Date’ >=startOfWeek(2)};”{color:brown}*Due beyond Two weeks*{color}”
)
)
Now that you’ve got the hang of these queries, don’t stop here. Experiment with different functions and operators to see how they can work for your specific needs.
The more you play around with JQL, the more you’ll discover its potential to streamline your workflow.
Dive in, get creative, and watch your project management game reach new heights.
Happy querying!
Discover more from LR Virtual Classroom
Subscribe to get the latest posts sent to your email.
LR Virtual Classroom 