10. JQL - JIRA Query Language

10.1. Where to find?

• Issues -> Search for Issues

  • Basic -> Advanced

  • Detail View -> List View

• Konfiguracja kolumn wyświetlania

10.2. Where is used?

• Searching Issues

• Board Configuration

• Filters for Dashboard

• Filters for Subscriptions

• Bulk edit (to change limit: echo 'jira.bulk.edit.limit.issue.count = 1000' >> $JIRA_HOME/jira-config.properties)

• jira.issue.editable = true dla statusu Done (Workflow)

10.3. Operators

Table 10.1. Operators

Operator	Description
=	Equals
!=	Not equal (is different than)
>	Greater than
<	Less than
<=	Greater or equal
>=	Less or equal
~	Contains text
(...)	List
AND	Conjunction
OR	Disjunction
ORDER BY	Ordering
ASC	Ascending
DESC	Descending

10.4. View

• Konfiguracja kolumn wyświetlania

• Import / Export CSV

  • All fields

  • current fields

• Limit wyświetlania wyników dla JQL (change: General Configuration -> Advanced Settings -> jira.search.views.default.max)

10.5. JQL Examples

• Operators capital letter

10.5.1. Select issues

project = "MYPROJECT"

status = "To Do"

assignee = "admin"

reporter = "myusername"

summary ~ "Hello"

summary ~ "Hell*"

summary ~ "*ell"

summary ~ "*ell*"

assignee != "myusername"

statusCategory = "To Do"

statusCategory != "Done"

Flagged IS NOT EMPTY

10.5.2. Ordering

project = "MYPROJECT"
    ORDER BY priority DESC

project = "MYPROJECT"
    ORDER BY priority DESC, key ASC

10.5.3. Complex queries

project = "MYPROJECT"
    AND status = "In Progress"

status = "To Do"
    OR status = "In Progress"

status IN ("To Do", "In Progress")

status NOT IN ("To Do", "In Progress")

statusCategory NOT IN ("To Do", "Done")

project = "MYPROJECT"
    AND resolution NOT IN ("Done", "Won't Do")

project = "MYPROJECT"
    AND assignee = "admin"
    AND (Flagged IS NOT EMPTY
         OR updated >= -1d
         OR statusCategory = "In Progress")

10.5.4. Functions

• https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-functions/

• https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-functions-reference-939938746.html

Table 10.2. JQL functions in Jira Core

Function	Description
cascadeOption()	Search for issues that match the selected values of a 'cascading select' custom field
componentsLeadByUser()	Find issues in components that are led by a specific user
currentLogin()	Perform searches based on the time at which the current user's session began
currentUser()	Perform searches based on the currently logged-in user
earliestUnreleasedVersion()	Perform searches based on the earliest unreleased version in a project
endOfDay()	Perform searches based on the end of the current day
endOfMonth()	Perform searches based on the end of the current month
endOfWeek()	Search for issues that are due by the end of the last day of the current week
endOfYear()	Perform searches based on the end of the current year
issueHistory()	Find issues that you have recently viewed, i.e. issues that are in the 'Recent Issues' section of the 'Issues' drop-down menu
issuesWithRemoteLinksByGlobalId()	Perform searches based on issues that are associated with remote links that have any of the specified global ids
lastLogin()	Perform searches based on the time at which the current user's previous session began
latestReleasedVersion()	Perform searches based on the latest released version (i.e. the most recent version that has been released) of a specified project
linkedissue	Searches for epics and subtasks. If the issue is not an epic, the search returns all subtasks for the issue
linkedIssues()	Searches for issues that are linked to an issue
membersOf()	Perform searches based on the members of a particular group
now()	Perform searches based on the current time
parentEpic()	Search for issues and sub-tasks that are linked to an epic
projectsLeadByUser()	Find issues in projects that are led by a specific user
projectsWhereUserHasPermission()	Find issues in projects where you have a specific permission
projectsWhereUserHasRole()	Find issues in projects where you have a specific role
releasedVersions()	Perform searches based on the released versions (i.e. versions that your Jira administrator has released) of a specified project
standardIssueTypes()	Perform searches based on 'standard' Issue Types, that is, search for issues that are not sub-tasks
startOfDay()	Perform searches based on the start of the current day
startOfMonth()	Perform searches based on the start of the current month
startOfWeek()	Search for new issues created since the start of the first day of the current week
startOfYear()	Perform searches based on the start of the current year
subtaskIssueTypes()	Perform searches based on issues that are sub-tasks
unreleasedVersions()	Perform searches based on the unreleased versions (i.e. versions that your Jira administrator has not yet released) of a specified project
updatedBy()	Search for issues that were updated by a specific user, optionally within the specified time range
votedIssues()	Perform searches based on issues for which you have voted
watchedIssues()	Perform searches based on issues that you are watching

Table 10.3. JQL functions in Jira Software

Function	Description
closedSprints()	Search for issues that are assigned to a completed Sprint
futureSprints()	Search for issues that are assigned to a sprint that hasn't been started yet
openSprints()	Search for issues that are assigned to a sprint that was started, but has not yet been completed

Table 10.4. JQL functions in Jira Service Management

Function	Description
approved()	Search for requests that required approval and have a final decision of approved
approver()	Search for requests that require or required approval by a user
breached()	Returns issues that whose most recent SLA has missed its goal
completed()	Returns issues that have an SLA that has completed at least one cycle
elapsed()	Returns issues whose SLA clock is at a certain point relative to a cycle's start event
everbreached()	Returns issues that have missed one of their SLA goals
myApproval()	Search for requests that require approval or have required approval by the current user
myPending()	Search for requests that require approval by the current user
organizationMembers()	Search for all requests sent by the members of an organization
paused()	Returns issues that have an SLA that is paused due to a condition
pending()	Search for requests that require approval
pendingBy()	Search for requests that require approval by a certain user
remaining()	Returns issues whose SLA clock is at a certain point relative to the goal
running()	Returns issues that have an SLA that is running, regardless of the calendar
withinCalendarHours()	Returns issues that have an SLA that is running according to the SLA calendar

assignee = currentUser()

Sprint IN closedSprints()

Sprint IN openSprints()

Sprint IN futureSprints()

10.5.5. Queries in History

project = "MYPROJECT"
    AND status WAS "Done"
    AND status != "Done"

project = "MYPROJECT"
    AND status WAS "Done"
    AND status != "Done"
    AND updated > -1d

due >= "2000-01-01" AND due <= "2000-01-31"

due >= startOfMonth() AND due <= endOfMonth()

due >= startOfMonth(-1w) AND due <= endOfMonth(+2w)

due <= now()
    AND statusCategory != "Done"

status WAS IN ("Done", "Rejected")

status WAS NOT "In Progress" BEFORE "2000-01-01"

status WAS NOT IN ("Done", "Rejected") BEFORE "2000-01-01"

status WAS "Resolved" BY "admin" BEFORE "2000-01-01"

status WAS "Resolved" BY "admin" DURING ("2000-01-01", "2000-01-31")

status CHANGED BY currentUser()

AFTER "date"
BEFORE "date"
BY "username"
DURING ("date1", "date2")
ON "date"
FROM "oldvalue"
TO "newvalue"

assignee CHANGED

priority CHANGED BY "admin"

priority CHANGED BY "admin" AFTER startOfWeek()

priority CHANGED BY "admin" AFTER startOfWeek() BEFORE endOfWeek()

priority CHANGED BY "admin" DURING ("2000-01-01", "2000-01-31")

status CHANGED
    FROM "In Progress"
    TO "Open"

status CHANGED
    FROM "In Progress"
    TO "Open"
    BY "admin"

status CHANGED
    FROM "In Progress"
    TO "Open"
    BY "admin"
    DURING ("2000-01-01", "2000-01-31")

status CHANGED
    FROM "In Progress"
    TO "Open"
    BY "admin"
    AFTER startOfWeek()
    BEFORE endOfWeek()

status CHANGED
    FROM "In Progress"
    TO "Open"
    BY membersOf("jira-administrators")
    AFTER startOfWeek()
    BEFORE endOfWeek()

10.6. Useful Queries

10.6.1. My issues To Do

assignee = currentUser()
    AND statusCategory != "Done"

assignee = currentUser()
    AND statusCategory != "Done"
    ORDER BY priority DESC, key ASC

project = "MYPROJECT"
    AND statusCategory != "Done"
    AND sprint IN openSprints()
    AND assignee = currentUser()
    ORDER BY priority DESC, key ASC

10.6.2. Tracking reported issues

reporter = currentUser()
    AND statusCategory != "Done"
    AND assignee != currentUser()

project = "IT Support"
    AND reporter = currentUser()
    AND statusCategory != "Done"

10.6.3. Tracking team members work

statusCategory = "In Progress"
    AND assignee IN membersOf("jira-administrators")

project = "MYPROJECT"
    AND assignee IN membersOf("jira-administrators")
    AND updated >= -7d

assignee IN membersOf("jira-administrators")
    AND updated >= startOfWeek()
    AND updated <= endOfWeek()

10.6.4. Daily

project = "MYPROJECT"
    AND sprint IN openSprints()
    AND (Flagged IS NOT EMPTY
         OR updated >= -1d
         OR statusCategory = "In Progress")

10.7. More info

• https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-939938733.html

• https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-functions-reference-939938746.html

10.8. Demonstration

• Change: list view, add headers, sort headers, order data

• Change: basic -> advanced

• Show: export CSV, bulk change

• Basic: select project, status, other fields, due date range

• Advanced: autocompletion, scalar and list operators, functions

10.9. Assignments

10.9.1. JQL Search View

1. Z menu u góry wybierz Issues -> Search for Issues

2. Change View [przycisk po prawej stronie] zmień na List View

3. Columns [przycisk po prawej stronie]: Odznaczyć: Created, Updated, Development

4. Columns: zaznaczyć: Summary, Issue Type, Due Date, Fix Version/s, Epic Link

5. Chwytając nagłówek kolumny, przenieś Issue Type (T) jako pierwsza kolumna

6. Ustawić kolumny w kolejności: Issue Type, Issue Key, Epic Link, Fix Version/s, Due Date, Status, Summary

7. Dodać kolumny: Original Estimate, Remaining Estimate, Time Spent

8. Z menu po prawej stronie u góry wybieramy Export -> CSV (Current Fields) -> Delimiter -> Comma (,)

10.9.2. JQL Search Basic

1. Z menu u góry wybierz Issues -> Search for Issues

2. Upewnij się, że jesteś w trybie wyszukiwania: Basic

3. Project -> swój projekt

4. Kliknij na nazwę kolumny Due Date dwukrotnie aby posortować rosnąco

5. Status -> In Progress oraz Blocked

6. More -> Due Date -> Now Overdue

7. Zmień zakres Due Date -> od 1/Jan/00 do 31/Jan/00

8. Zmień zakres Due Date -> Due in next 8 hours or is overdue

9. Zmień zakres Due Date -> In range -7d to ...` [pozostaw niewypełnione]

10.9.3. JQL Search Advanced

1. Z menu u góry wybierz Issues -> Search for Issues

2. Upewnij się, że jesteś w trybie wyszukiwania: Advanced

3. Kliknij link Advanced z paska wyszukiwania

4. To co wpisujesz w tym polu, to tzw. JQL (Jira Query Language)

5. W polu wyszukiwania wpisz literę p i zobacz co Jira Ci podpowiedziała

6. Wybierz strzałką na klawiaturze pozycję project i kliknij enter

7. Z listy wybierz znak równa się =

8. Z listy wybierz nazwę swojego projektu (można najechać i kliknąć myszką)

9. Klikamy enter aby wyszukać, powinno nam to wyświetlić wszystkie zadania z naszego projektu

10. Kliknij w pole wyszukiwania i po fragmencie, który wcześniej był wpisany dodaj spację i zobacz co Ci podpowiada

11. Wybierz AND i zacznij pisać status -> mamy dwie opcje do wyboru: status i statusCategory

12. Wybierz statusCategory -> następnie równa się = -> In Progress i klikamy enter aby wyszukać zadania

13. Edytuj zapytanie i dopisz na koniec: Epic Link -> równa się = -> wybrać Epic Wyszukiwarka, ale z Twojego projektu

14. Wyczyść zapytanie

15. w poniższych zapytaniach MYPROJECT zamień na klucz swojego projektu

16. Wyszukaj: project = MYPROJECT AND fixVersion = earliestUnreleasedVersion()

17. Wyszukaj: assignee = currentUser() and statusCategory != Done

10.9.4. JQL Search Bulk Change

1. Z menu u góry wybierz Issues -> Search for Issues

2. Upewnij się, że jesteś w trybie wyszukiwania: Advanced

3. Wyszukaj: project = MYPROJECT and due IS EMPTY (gdzie MYPROJECT to nazwa Twojego projektu)

4. Przycisk Tools (po prawej u góry) -> Bulk Change -> all X issue(s)

5. Zaznacz wszystkie (checkboxem do zaznaczania wszystkich na raz, nie rób tego pojedynczo)

6. Kliknij przycisk Next -> Edit Issues -> Next

7. Zmień Change Due Date i ustaw na 1/Nov/00

8. Kliknij przycisk Next (na dole) -> Confirm -> Ok, got it