Function JQL
- Piotr Bojko
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
SELECT * FROM TABLE(AUX.JQL('KEY, ASSIGNEE','project = OKO'))
SELECT * FROM TABLE(AUX.JQL('ID, Reflector, Resignedly, customfield_10001','')) ORDER BY ID LIMIT 3
SELECT COUNT(ID) AS CNT FROM TABLE(AUX.JQL('ID','filter = "Dev open issues"'))
SELECT COUNT(ID) AS CNT FROM TABLE(AUX.JQL('Key, ASSIGNEEOBJECT PATH `emailAddress` AS EMAIL','filter = "Dev open issues"'))