Handling ANY (java objects) type with XPATH
In some cases your query in Smart QL will handle java objects from Jira model:
- When using AUX.JQL function, some fields available under projection parameter are java objects from Jira, like collection of labels (Collection<Label>), affected versions (Collection<Version>), fix versions and others.
- The same applies to AUX.GETFIELD function. You can use any field here available in AUX.JQL.
Smart QL does not convert those fields for further processing in SQL, because we would lock down the possibilities for you. It rather lets the conversion for you to feet your specific needs. In other words, Smart QL does not convert collection of labels to some fancy string like label1, label2, label3 because such value in a collumn is hard to process further. Instead of doing this Smart QL leaves the raw java object Collection<Label> and gives you tools to convert this to any information you need in your query. This could be the count of labels per issue, or boolean information whether issue is labelled with specific value. It is solely up to you.
The mentioned tool the Smart QL gives you is ability to handle java objects with XPATH expressions in AUX.PATH function directly and for your convenience in AUX.SQL and AUX.GETFIELD when declaring a field.
See XPath intro and JXPath user guide for greater details on XPATH syntaxt and semantics.
Java types
Smart QL can handle following java types:
| Type | Documentation | Field name (use it in AUX.JQL and AUX.GETFIELD) | Notes |
|---|---|---|---|
| ApplicationUser | Javadocs | ASSIGNEEOBJECT, CREATOROBJECT, REPORTEROBJECT | |
| Project | Javadocs | PROJECTOBJECT | |
| Version | Javadocs | AFFECTEDVERSIONS, FIXVERSIONS | Mentioned fields returns collection of Versions - Collection<Version> in Java. |
| ProjectComponent | Javadocs | COMPONENTS | Field returns collection of Compoents - Collection<Version> in Java. |
| Attachment | Javadocs | ATTACHMENTS | Field return collection - Collection<Attachment> in Java. |
| Label | Javadocs | LABELS | Field return collection - Collection<Label> in Java. |
| IssueType | Javadocs | ISSUETYPEOBJECT |
Collections
Having several fields of issue as Collection of java objects is the main reason that, Smart QL does not handle all issue fields on its own and convert it to SQL types. Flattening collection to a column/row value loses information, whether letting handling them by user with XPATH expressions gives more flexibility and power. See examples:
SELECT *
FROM TABLE(AUX.JQL('Key, AffectedVersions PATH `count(.)` AS `AFFVERNO`', 'Project = "Sample Project"')) JQL
WHERE JQL.AFFVERNO > 1
SELECT AUX.ASISSUEKEY(i.id), AUX.GETFIELD(i.id, 'FixVersions PATH `.[1]/name`') FIRST_FIX_VERSION FROM issues i WHERE i.votes > 0
SELECT AUX.ASISSUEKEY(i.id), AUX.GETFIELD(i.id, 'Sprint PATH `boolean(state="CLOSED")`') FROM issues i