Custom JQLs (built on the fly)
Since the 7.17.0 version, the sql function requires a query ID ad the first parameter. It is no longer possible to run an arbitrary query from the Issue Navigator: only trusted users can write and save queries to get a Query ID
It also supports parameters now...
sql(#ID, parameters,...)
About the custom JQLs
- It allows trusted users to build a catalog of parametrizable JQL functions shareable with the rest of the organization.
- It allows you to take the control of how the JQL functions work as opposed to the opacity of the JQL functions provided by most of the apps available from the Atlassian Marketplace
- The result is standard JQL functions that run in the context of the calling user honoring Jira permissions at the core level to enrich your Jira reporting capabilities.
Example: Get the issues with a given number of comments
Step 1) Write the initial query from the console
SELECT key(jql.issue) AS "Issue", nvl2(ic.id, 1, 0)as "#Comments" FROM JQL LEFT JOIN issuecomments ic ON ic.issueid = jql.issueid WHERE jql.query = 'project = DEMO' GROUP BY "Issue" HAVING "#Comments" = 0
In the example above the query returns all the issues in the DEMO project with no comments.
Ensure that the first column returns an Issue KEY (plain text with no HTML markup) or Issue ID (Numeric)
Step 2 (Optional) Generalize the SQL query by adding parameters
Replace the constant with the SQL variable symbol → ?
SELECT key(jql.issue) AS "Issue", nvl2(ic.id, 1, 0)as "#Comments" FROM JQL LEFT JOIN issuecomments ic ON ic.issueid = jql.issueid WHERE jql.query = ? GROUP BY "Issue" HAVING "#Comments" = ?
Note that two variables have been introduced by replacing the JQ.QUERY value (project = DEMO) and the HAVING "#Comments" condition (0)
Step 3) Save the query
Run the query and click on the Save button anf fill out a name (#Comments in this example) and a description (Optional).
NOTE: If the query has parameters then you get an error as the console does not support parameters. Please, ignore them and go forward saving the parameterized query.
Step 4) Get the Saved query ID
Click on the Saved queries link to get your new Saved query ID (1159 in this example)
Step 5) Run the SQL from the Issue Navigator
issue in sql(1159, "project=DEMO", 0)
Use the built-in sql() JQL function to run your query. Provide values for all the parameters (if any)
Notes
Double transformation
Pay attention to the subtle double transformation: JQL → SQL → JQL
You started with a JQL
project=DEMO
Then you transformed it into SQL
SELECT ... FROM JQL JOIN ... WHERE JQL:QUERY = 'poject= DEMO' AND ...
And finally, it was transformed to JQL again:
issue in sql(..)
Nesting (N-transformation level)
Since the built-in sql() JQL function is a standard JQL function it can be used as an input of a new SQL query (JQL → SQL → JQL → SQL → JQL...)
SELECT * FROM JQL WHERE JQL.QUERY = 'issue in sql(1159, "project="DEMO", 0)'
In the example above, the SQL query will internally invoke another SQL