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. 

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