What is JQL

JQL stands for "Jira Query Language," and it is a search language used to query Jira data. JQL allows users to search for issues in Jira by specifying criteria such as issue type, status, project, assignee, and custom fields.

JQL functions

JQL queries can make use of JQL functions to augment search capabilities , and thus go beyond simple search queries based on the "attributes (i.e., custom fields) of the Jira issues .

A function in JQL appears as a word followed by parentheses, which may contain one or more explicit values or Jira fields. In a clause, a function is preceded by an operator, which in turn is preceded by a field. A function performs a calculation on either specific Jira data or the function's content in parentheses, such that only valid results are retrieved by the function, and then again by the clause in which the function is used.

JQL functions are among the extension points that JQL provides to developers. Functions provide a way for values within a JQL query to be calculated at runtime. They are simple to write and can be surprisingly powerful. For example, consider the issueKey clause in JQL, it matches an issue with a particular issue key.

Jira has its own JQL functions available but other Jira applications or through custom development we can define new functions and make them available to be used.

Custom Fields

Jira allows the creation of custom fields to enhance the precision of issue queries. This flexibility is vital for tailoring searches to meet specific project requirements. Additionally, Xray, a Jira add-on for test management, introduces its own set of custom fields. These fields provide further refinement options, enabling more detailed and specific queries related to testing activities. By leveraging both Jira’s and Xray’s custom fields, users can conduct highly customized searches, improving the efficiency of issue tracking and management.

Limitations of JQL

JQL, while powerful within the Jira environment, has several limitations. It can only query data within Jira, lacking the ability to handle cross-database operations or integrate with external data sources as SQL can. JQL is also limited to predefined fields and does not support complex calculations or transformations. Additionally, JQL lacks some advanced querying features like joins and subqueries, which are standard in SQL. These constraints make JQL less versatile for comprehensive data analysis and manipulation compared to SQL but the ideal form to navigate the information present in Jira.

JQL vs SQL

JQL is extremely useful for pinpointing specific issue data within Jira, such as assignee, status, and priority. It’s like picking a single book from a library shelf. In contrast, SQL operates across various databases, linking and manipulating diverse data types like strings and integers. JQL is easily accessible within Jira, while SQL requires a client with the right permissions to access the database. Plus, JQL queries always return issues, whereas SQL can return various data types.

Examples

Below we have a list of examples using JQL queries for some of the most common cases we have seen.

The examples are divided per returned entity and contain (every time it is possible) one example with JQL functions and the same example with Custom fields.

Requirements

Returns a list of uncovered requirements

project = COM AND issue in requirements('uncovered')

project = COM AND issuetype = Story  and "Requirement Status" in ("UNCOVERED")

Returns a list of requirements tested and OK, accordingly with latest results

project = COM AND issue in requirements('OK')

project = COM AND issuetype = Story  and "Requirement Status" in ("OK")

Returns a list of requirements tested and OK for version X

issue in requirements("OK", "COM", "1.2")

project = COM AND issuetype  = Story  and "Requirement Status" in ("OK") and fixVersion in (1.2)

Returns a list of requirements tested and OK for version X, for environment Y

issue in requirements("OK", "COM", "1.2","Chrome")

Returns a list of requirements tested but with failures, on version X

issue in requirements("NOK", "COM", "1.2")

Returns a list of requirements tested but with failures, on version X, environment Y

issue in requirements("NOK", "COM", "1.2", “Chrome”)

Returns all requirements associated to a test plan (directly or indirectly)

issue in testPlanRequirements("COM-99")

Defects

Returns a list of defects reported while testing version X

First create filter with the below query and save it with name: DefectsOfVersion1: 

issuetype = 'Test' and issue in testsWithReqVersion('COM', '1.0')

Then use the filter with the query:

issue in defectsCreatedDuringTesting("DefectsOfVersion1")

Returns a list of defects created during test execution of tests covering the specified requirement(s)

issue in defectsCreatedForRequirement("REQ-123")

Returns a list of defects created during a test execution by a given user

issue in defectsCreatedDuringTestExecution(TEST-123, 'user1|user2')

Tests

Returns a list of tests in a given Test Repository folder (e.g. “parent/child”)

issue in testRepositoryFolderTests("CALC", 'parent/child') 

Returns a list of orphan tests in a given project (i.e., that are not yet organized in a folder within the Test Repository of the respective project)

project = CALC and  issuetype = Test and issue not in  testRepositoryFolderTests("CALC", '')

Returns a list of tests that do not have an associated test execution

issuetype = Test and issue in testsWithoutTestExecution()

Returns a list of manual Tests without steps

issue in manualTestsWithoutSteps()

Returns all manual tests with text present in test steps

issuetype = 'Test' and "Manual Test Steps" ~ "Login with user administrator"

Returns all manual tests with 3 steps

issuetype = 'Test' and "Steps Count" = 3

Returns all tests associated to a test plan

issue in testPlanTests("DEMO-10")

Returns all failed tests associated to a test plan

issue in testPlanTests("DEMO-10","FAIL")

issuetype = Test and TestRunStatus = "DEMO-10 - FAIL"

Returns all passed tests associated to a test plan for a given environment

issue in testPlanTests("DEMO-10","PASS","IOS")

issuetype = Test and TestRunStatus = "DEMO-10 - PASS environment:IOS"

Returns a list of failed tests assigned to User A that have an associated defect, include comments, lack evidence, and fall within the specified date range related to the test execution

issuetype = 'Test' and issue in testExecutionTests('CALC-397','FAIL','User A','true','','true','false','>2024-05-31','<2024-06-30')

Returns a list of Tests that are not part of a Test Set

issue in testsWithNoTestSet()

Returns a list of Tests associated to a Test Set for a given version

issuetype = 'Test' and issue in testsWithTestSetVersion( 'DEMO', 'v1.0')

Returns all tests within one Test Set 

issuetype = 'Test'  and key in testSetTests('DEMO-5')

Returns all tests contained in a folder of a Test Plan

issue in testPlanFolderTests(CALC-10, 'Parent/Child')

Returns all Tests contained in a folder  of the Test Plan with the failed status for the environment “windows”

issue in testPlanFolderTests(CALC-10, 'Parent/Child', "true", "FAIL", "windows")

Test plans

Returns a list of Test Plans associated to a Test

issuetype = 'Test Plan' and key in testTestPlan('DEMO-1')

Returns the Test Plan list that has Test Runs finished since the given date

issue in testPlansWithCompletedTestRunsSince(2024-01-01)

Test Executions

Returns a list of Test executions assigned to a specific user

issuetype = 'Test Execution' and issue in testExecWithTestRunsAssignedToUser('userDPC')

Returns all Test Executions of one Test Plan

issue in testPlanTestExecutions("DEMO-10")

Returns the list of failed Test Executions of a Test

issuetype = 'Test Execution' and issue in testTestExecutions('COM-180', 'FAIL')

Test Sets

Returns the list of Test Sets associated to the test

issuetype = 'Test Set' and key in testTestSet('DEMO-1')

Returns a list of Test Sets that have (at least) one Test in the Test Execution

issuetype = 'Test Set' and issue in testSetPartiallyIn('testExecList')

Returns a list of Test Sets that have (at least) one Test in the Test Plan

issuetype = 'Test Set' and issue in testSetPartiallyIn('testPlanList')

Returns a list of Test Sets that have all its Tests in the Test Execution

issuetype = 'Test Set' and issue in testSetFullyIn('testExecList')

Tips

• Make sure to use the correct values of your Xray instance in the above examples (valid users, valid Keys, valid statuses, environment and dates) or the queries will return empty lists
• You can save some these queries into a filter for later use (in other queries or reports)

References

• Jira JQL functions
• Jira advance searching
• Xray Enhanced query with JQL