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
  • JQL query (using functions):
    • project = COM AND issue in requirements('OK')
  • JQL query (using custom fields):
    • project = COM AND issuetype = Story  and "Requirement Status" in ("OK")
• Returns a list of requirements tested and OK for version X
  • JQL query (using functions):
    • issue in requirements("OK", "COM", "1.2")
  • JQL query (using custom fields):
    • 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
  • JQL query (using functions):
    • issue in requirements("OK", "COM", "1.2","Chrome")
• Returns a list of requirements tested but with failures, on version X
  • JQL query (using functions):
    • issue in requirements("NOK", "COM", "1.2")
• Returns a list of requirements tested but with failures, on version X, environment Y
  • JQL query (using functions):
    • issue in requirements("NOK", "COM", "1.2", “Chrome”)
• Returns all requirements associated to a test plan (directly or indirectly)
  • JQL query (using functions):
    • issue in testPlanRequirements("COM-99")

Defects

• Returns a list of defects reported while testing version X
  • First: Create filter with (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")
• JQL query (using functions): 
• Returns a list of defects created during test execution of tests covering the specified requirement(s)
  • issue in defectsCreatedForRequirement("REQ-123")
• JQL query (using functions):
• Returns a list of defects created during a test execution by a given user:
  • issue in defectsCreatedDuringTestExecution(TEST-123, 'user1|user2')
• JQL query (using functions):

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()
• JQL query (using functions):
• Returns a list of manual Tests without steps
  • issue in manualTestsWithoutSteps()
• JQL query (using functions):
• Returns all manual tests with text present in test steps
  • issuetype = 'Test' and "Manual Test Steps" ~ "Login with user administrator"
• JQL query (using custom fields):
• Returns all manual tests with 3 steps:
  • issuetype = 'Test' and "Steps Count" = 3
• JQL query (using custom fields):
• Returns all tests associated to a test plan
  • issue in testPlanTests("DEMO-10")
• JQL query (using functions):
• Returns all failed tests associated to a test plan:
  • issue in testPlanTests("DEMO-10","TODO")
  • issuetype = Test and TestRunStatus = "DEMO-10 - NOK"
• JQL query (using functions):
• JQL query (using custom fields):
• Returns all passed tests associated to a test plan for a given environment:
  • issue in testPlanTests("DEMO-10","TODO","IOS")
  • issuetype = Test and TestRunStatus = "DEMO-10 - TODO environment:IOS"
• JQL query (using functions):
• JQL query (using custom fields):
• 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','FAILED','User A','true','','true','false','>2024-05-31','<2024-06-30')
• JQL query (using functions):
• Returns a list of Tests that are not part of a Test Set
  • issue in testsWithNoTestSet()
• JQL query (using functions):
• Returns a list of Tests associated to a Test Set for a given version
  • issuetype = 'Test' and issue in testsWithTestSetVersion( 'DEMO', 'v1.0')
• JQL query (using functions):
• Returns all tests within one Test Set 
  • issuetype = 'Test'  and key in testSetTests('DEMO-5')
• JQL query (using functions):
• Returns all tests contained in a folder of a Test Plan
  • issue in testPlanFolderTests(CALC-10, 'Parent/Child')
• JQL query (using functions):
• 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")
• JQL query (using functions):

Test plans

• Returns a list of Test Plans associated to a Test
  • issuetype = 'Test Plan' and key in testTestPlan('DEMO-1')
• JQL query (using functions):
• Returns the Test Plan list that has Test Runs finished since the given date
  • issue in testPlansWithCompletedTestRunsSince(2024-01-01)
• JQL query (using functions):

Test Executions

• Returns a list of Test executions assigned to a specific user:
  • issuetype = 'Test Execution' and issue in testExecWithTestRunsAssignedToUser('userDPC')
• JQL query (using functions):
• Returns all Test Executions of one Test Plan
  • issue in testPlanTestExecutions("DEMO-10")
• JQL query (using functions):
• Returns the list of failed Test Executions of a Test
  • issuetype = 'Test Execution' and issue in testTestExecutions('COM-180', 'FAIL')
• JQL function:

Test Sets

• Returns the list of Test Sets associated to the test
  • issuetype = 'Test Set' and key in testTestSet('DEMO-1')
• JQL query (using functions):
• Returns a list of Test Sets that have (at least) one Test in the Test Execution
  • issuetype = 'Test Set' and issue in testSetPartiallyIn('testExecList')
• JQL queries:
• Returns a list of Test Sets that have (at least) one Test in the Test Plan
  • issuetype = 'Test Set' and issue in testSetPartiallyIn('testPlanList')
• JQL queries:
• Returns a list of Test Sets that have all its Tests in the Test Execution
  • issuetype = 'Test Set' and issue in testSetFullyIn('testExecList')
• JQL query:

Tips

• To see the history and success/failure details, you can navigate to Manage Apps -> Xporter -> Xporter Audit Logs.
  
• You can minimize the email clutter by enabling this toggle in the Configuration section.
  
• Each scheduled report can support multiple Actions, so you may choose to store files in a server in addition to the email delivery.
• Meaningful report names help with file management in the long run.

References