JQL and SQL (working together in the same query)

Owned by Pablo Beltran (Unlicensed)
Last updated: Nov 18, 2020 by Pablo Beltran
2 min read

SQL+JQL is built on top of the popular H2 database engine. H2 is a high-performance database, however, SQL+JQL resolves all the queries against the Jira Java API, checking users' permissions, etc.


JQL vs SQL strings


Strings:

  • single quotes inĀ SQL
  • double quotes inĀ JQL


SELECT
Ā KEY(JQL.ISSUE)
FROM
Ā  JQL
WHERE
Ā  JQL.QUERY = ' status = "Open" '


Pay attention to the condition:

JQL.QUERY = ' status = "Open" '

In the query above:

"Open"

is a string in a JQL that will be parsed by Jira,Ā  whereas:Ā 

' status = "Open" '

is a string that will be parsed by the SQL+JQL engine.

Make Jira do as much work as possible

Please ensure that you understood this section properly as this feature is essential to write SQL queries efficiently in many casesĀ 

JQL performs much better than SQL+JQL. So, let Jira do as much work as possibleĀ ā†’ Move conditions into JQL as much as possible, instead of SQL.

Example:

SELECT *
FROM 
  issues i
WHERE 
  i.jql = ' reporter = currentUser() ' 
   AND
  i.assignee = 'John Smith'


The query above will get all the current user's issues. Then, the engine will check whether the assignee isĀ John Smith forĀ all the fetched issues. If the currentUser() reported N issues, checking the assignee by the SQL+JQL engine will occur N times.


However, the query below the SQL+JQL engine will rely on the Jira JQL engine to search for all the current user's issues assigned to John Smith which will return M records where M <= N.


SELECT *
FROM 
  issues i
WHERE 
  i.jql = ' reporter = currentUser() AND assignee = "John Smith" '


Joining SQL tables via dynamic JQL


Please ensure that you understood this section properly as this feature is essential to write SQL queries efficiently in many casesĀ 


I.e: how to get all the issues fixing any version of a project?


SELECT 
  key(jql.issue) as "Issue" 
FROM
 PROJECTVERSIONS pv 
    JOIN
 JQL ON query = 'fixVersion = ' || pv.id
WHERE
  pv.projectid = 10001


Note that the JQL query is built dynamically:Ā 


'fixVersion = ' || pv.id

taken the project verion id as input.

The result of the above sentence is a text string that will be executed and resolved by the JQL table returning all the issues in that project version.


Next steps

Read about theĀ Performance