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

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.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