Enhancing JQL queries with SQL features

Having a JQL query or filter can be transformed to more informative data by using SmartQL SQL features. First of all - you can apply aggregates onto you JQL quey, see example:

SELECT issuetype, status, COUNT(issuekey), MIN(created), MAX(updated)
FROM TABLE(AUX.JQL('issuetype,status,issuekey,created,updated', 'project = SSP'))
GROUP BY issuetype, status

This query treats issues foung from JQL 'project = SSP' as a table with columns 'issuetype,status,issuekey,created,updated'. Example results can be following:

Joining JQL data with other SQL views available in SmartQL

Result of JQL queries can be joined with views and tables from SmartQL. Previous example query can be updated:

SELECT it.NAME `Issue type`, s.NAME `Status`, COUNT(issuekey), MIN(created), MAX(updated)
FROM TABLE(AUX.JQL('project, issuetype,status,issuekey,created,updated', 'project = SSP')) j
JOIN ISSUETYPES it ON it.ID = j.issuetype
JOIN STATUSES s on s.ID = j.status
GROUP BY it.NAME, s.NAME

Example results can be following:

Lateral joining JQL with SQL

JQL queries can also be computed for each row of other table or view, by using lateral joins.  Following query shows first 5 agile board to issue mappings.

SELECT b.NAME, j.key
FROM SOFTWARE.BOARDS b
JOIN CORE.FILTERS f on f.ID = b.SAVEDFILTER
CROSS JOIN LATERAL TABLE(AUX.JQL('key', f.query)) j
LIMIT 5

Example result:

Using quotes across JQL and SQL in one query