Please, read about AUX.JQL function which allows you to use JQL inside SQL.

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:

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

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:

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

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:

NAME	key
SSP board	SSP-1
SSP board	SSP-2
SSP board	SSP-3
SSP board	SSP-4
SSP board	SSP-5

Using quotes across JQL and SQL in one query

Double quotes " are for JQL strings.

Single quotes ' are for SQL strings.

Backquotes ` are for boundaries of SQL columns names.

Browser not supported