This function does not expose all data, but only those which are available for user due to his JIRA permissions

This function enables power of JQL in you SQL queries. It returns the data in table format just like the builtin JQL navigator, so you can use it in your SQL queries.

AUX.JQL function is a table function - it produces a table as a result. This table you can use in your queries just like regular table with following syntax.

TABLE(AUX.JQL(projection, jql)) alias

User should provide following parameters:

Type

Notes

String

Projection of fields to be used as column is a result table. Those are issue fields from JIRA,

Valid syntax is

[`]fieldname[`] [PATH `valid-xpath-expression`] [CAST `valid-sql-type`] [AS `column-alias`] [, ...another fields]

PATH, CAST, AS are keywords.

fieldname is obligatory, where PATH, CAST and AS subexpressions are not.

valid-xpath-expression is an XPATH expression, see Handling ANY (java objects) type with XPATH

valid-sql-type is a SQL datatype to be used when XPATH is used. This is optional, Smartl QL will always deduce by its own a valid type for you.

column-alias is an alias you want to use instead of a field name.

Please note the use of grave accents - ` . Grave accent are obligatory for valid-xpath-expression, valid-sql-type and column-alias. For fieldname grave accents can be omitted.

Examples:

'Key, Project, Summary'

'`Key`, `Project`, `Summary`'

'Key AS `ISSUEKEY`, Project, Summary'

'`Key`, `ReporterObject` PATH `emailAddress` AS `EMAIL_OF_REPORTER`'

JQL

Valid JQL query

Supported fields

Supported builtin fields are:

Name	Type	Notes
ID	BIGINT NOT NULL	Database ID of an issue - this is not an issue key.
CREATED	TIMESTAMP
ASSIGNEE	VARCHAR
ASSIGNEEOBJECT	ANY	Java object with class ApplicationUser, see Handling ANY (java objects) type with XPATH.
DUEDATE, DUE	TIMESTAMP
CREATOR	VARCHAR
CREATOROBJECT	ANY	Java object with class ApplicationUser, see Handling ANY (java objects) type with XPATH.
DESCRIPTION	VARCHAR
ENVIRONMENT	BIGINT
ESTIMATE, TIMEESTIMATE	BIGINT
ORIGINALESTIMATE, TIMEORIGINALESTIMATE	BIGINT
TYPE, ISSUETYPE	VARCHAR
ISSUETYPEOBJECT	ANY	Java object with class IssueType, see Handling ANY (java objects) type with XPATH.
KEY, ISSUEKEY	VARCHAR
PRIORITY	VARCHAR
PROJECT	BIGINT	Points to Table PROJECTS
PROJECTOBJECT	ANY	Java object with class Project, see Handling ANY (java objects) type with XPATH.
REPORTER	VARCHAR
REPORTEROBJECT	ANY	Java object with class ApplicationUser, see Handling ANY (java objects) type with XPATH.
RESOLVED, RESOLUTIONDATE	TIMESTAMP
RESOLUTION	VARCHAR
STATUS	VARCHAR	Points to Table STATUSES
SUMMARY	VARCHAR
TIMESPENT	BIGINT
UPDATED	TIMESTAMP
AFFECTEDVERSIONS	ANY	Java object with class Collection<Version>, see Handling ANY (java objects) type with XPATH.
FIXVERSIONS	ANY	Java object, with class Collection<Version>, see Handling ANY (java objects) type with XPATH.
COMPONENTS	ANY	Java object with class Collection<ProjectComponent>, see Handling ANY (java objects) type with XPATH.
VOTES	BIGINT
WATCHES	BIGINT
ATTACHMENTS	ANY	Java object with class Collection<Attachment>, see Handling ANY (java objects) type with XPATH.
LABELS	ANY	Java object with class Collection<Label>, see Handling ANY (java objects) type with XPATH.

Smart QL support also custom fields. You choose custom field by its name or use format customfield_{id}. Please see examples.

Examples

All issue keys and assigned users from project OKO

SELECT * FROM TABLE(AUX.JQL('KEY, ASSIGNEE','project = OKO'))

Usage of custom fields. Fields Reflector, Resignedly are custom called by their names. Custom field with id 10001 is called customfield_10001

SELECT * FROM TABLE(AUX.JQL('ID, Reflector, Resignedly, customfield_10001','')) ORDER BY ID LIMIT 3

Count issues from specific JQL filter

SELECT COUNT(ID) AS CNT FROM TABLE(AUX.JQL('ID','filter = "Dev open issues"'))

Emails to assigned to issues.

SELECT COUNT(ID) AS CNT FROM TABLE(AUX.JQL('Key, ASSIGNEEOBJECT PATH `emailAddress` AS EMAIL','filter = "Dev open issues"'))