Please, read about AUX.JQL function which allows you to use JQL inside SQL. |
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:
issuetype | status | EXPR$2 | EXPR$3 | EXPR$4 |
---|---|---|---|---|
10001 | 10001 | 8 | 2018-06-12 13:38:35 | 2018-07-08 00:18:35 |
10001 | 10000 | 8 | 2018-07-09 14:42:35 | 2018-07-10 18:52:38 |
10101 | 3 | 2 | 2018-07-06 21:44:35 | 2018-07-07 19:09:35 |
10001 | 3 | 1 | 2018-07-03 09:42:35 | 2018-07-03 09:42:35 |
10102 | 10001 | 1 | 2018-07-03 09:42:35 | 2018-07-07 02:18:35 |
10101 | 10000 | 1 | 2018-07-10 18:52:37 | 2018-07-10 18:52:37 |
10102 | 10000 | 2 | 2018-07-03 09:42:35 | 2018-07-10 18:52:38 |
For converting your Jira searches and any JQL you can start with Jira builtin navigator and then export prepared JQL query to SmartQL, see Exporting issue searches & JQL queries to 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:
Issue type | Status | EXPR$2 | EXPR$3 | EXPR$4 |
---|---|---|---|---|
Sub-task | To Do | 1 | 2018-07-10 18:52:37 | 2018-07-10 18:52:37 |
Story | Done | 8 | 2018-06-12 13:38:35 | 2018-07-08 00:18:35 |
Story | To Do | 8 | 2018-07-09 14:42:35 | 2018-07-10 18:52:38 |
Sub-task | In Progress | 2 | 2018-07-06 21:44:35 | 2018-07-07 19:09:35 |
Bug | Done | 1 | 2018-07-03 09:42:35 | 2018-07-07 02:18:35 |
Bug | To Do | 2 | 2018-07-03 09:42:35 | 2018-07-10 18:52:38 |
Story | In Progress | 1 | 2018-07-03 09:42:35 | 2018-07-03 09:42:35 |
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:
NAME | key |
---|---|
SSP board | SSP-1 |
SSP board | SSP-2 |
SSP board | SSP-3 |
SSP board | SSP-4 |
SSP board | SSP-5 |
Double quotes " are for JQL strings. |
Single quotes ' are for SQL strings. |
Backquotes ` are for boundaries of SQL columns names. |