What you'll learn
- What is JQL
- What are the diferences between SQL and JQL
- Examples of JQL queries
What is JQL
JQL stands for "Jira Query Language," 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 use 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, 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 allow 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 we can define new functions and make them available through other Jira applications or custom development.
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 advanced querying features like joins and subqueries, which are standard in SQL. These constraints make JQL less versatile for comprehensive data analysis and manipulation than SQL, but it is 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)