Writing conditional queries with Apache FreeMarker
Elements Connect supports the use of FreeMarker for dynamic query construction. FreeMarker is a powerful templating engine that allows you to create queries that can generate dynamic content based on data and conditions. This documentation provides an overview of how to use FreeMarker with Elements Connect Cloud.
FreeMarker language is available for database and REST API data sources:
For Connected Items: in field query and field template
For Custom Fields: in field query only
👉 We will work on adding FreeMarker in Custom Fields templates soon!
Using FreeMarker in queries
FreeMarker is used to create dynamic queries by allowing you to embed conditional logic and data-driven elements into your queries. This enables more flexible and powerful interactions with external data sources.
Basic Syntax
This section is a subset of what you can do with FreeMarker.
For a full guide, see the official Apache FreeMarker Manual.
Directives
You use FTL tags to call directives. There are two kind of FTL tags:
Start-tag:
<#directivename parameters>
End-tag:
</#directivename>
This is similar to HTML or XML syntax, except that the tag name starts with #
.
Comment Similar to HTML comments, but they are delimited by |
CODE
|
Use |
CODE
|
Access a variable Use |
CODE
|
Use |
CODE
|
Loops |
CODE
|
Expressions: built-in references & operations
Built-ins are like methods that are added to the objects by FreeMarker. To prevent name clashes with actual methods and other sub-variables, instead of dot (.
), you separate them from the parent object with question mark (?
).
Empty check method (for sequence) Use |
CODE
|
Join (for sequence)
|
CODE
|
URL Encoding (for string) Use to encode a string for use in a URL. |
CODE
|
Check on value (for sequence) |
CODE
|
Some other expressions that might come useful:
Arithmetic Operations |
CODE
|
Just the usual logical operators:
The operators will work with boolean values only. Otherwise an error will abort the template processing. |
CODE
|
Use To test two values for inequality you use |
CODE
|
Use |
CODE
|
Variables
Most of the variables that a typical template works with comes from the data-model. But templates can also define variables themselves, usually to hold loops variables, temporary results, macros, etc.
Issue variables The Issue variable is data that acts like a JSON. It stores other variables (the so called sub variables) by a lookup name (e.g., "priority", "reporter" or "key"). What you can retrieve from the issue:
|
CODE
|
Project variables
|
CODE
|
Other variables Many other variables can be used in FreeMarker templates. |
CODE
|
|
CODE
|
Use |
CODE
|
Example queries on Jira’s API
[if/else] Dynamic JQL Query Based on Priority
CODE<#if $issue.priority == "High"> /search?jql=priority=high <#elseif $issue.priority == "Low"> /search?jql=priority=low <#else> /search?jql=priority=medium </#if>
[?join][?size][if/else] Query Excluding Projects
Let’s assume customfield_10074 is a multiple value project picker fields, containing a list of project keys:
"customfield_10074": [
"PJT1",
"PJT2"
],
Then, the query to retrieve all issues except those related to previously selected projects could be:
<#if issue.customfield_10074?size != 0>
<#assign projects = issue.customfield_10074?join(",")>
/search?jql=project NOT IN (${projects})
<#else>
/search?jql=order by created DESC
</#if>
→ The ?join(",")
built-in is mandatory here to transform the array into a string for the JQL.
[assign][?split][?map][?join] Retrieve all ticket potentially linked to same topic
<#-- Extract keywords from the summary by splitting on spaces -->
<#assign keywords = issue.summary?split(" ")>
<#-- Join keywords with OR for a flexible JQL query -->
<#assign keywordQuery = keywords?map(word -> "summary ~ \"" + word + "\"")?join(" OR ")>
/search?jql=${keywordQuery} ORDER BY created DESC
That’s what this query could look like in a read-only custom field:
Using FreeMarker in templates
For Connected Items only, Freemarker can also be used to format the display of data within Elements Connect templates. Please check here for additional information.
Best Practices
Ensure your FreeMarker templates are correctly validated with the query tester to avoid runtime errors.
Optimize your queries and templates for performance, especially with large datasets.
For complex requests, don’t hesitate to use comments to facilitate maintenance
Usage limitations
For the moment:
FreeMarker is not available in the template part for Custom Fields (no conditional display is possible)
Number of characters in the query is limited to 5000. Let us know if you need more!
Additional Resources
For any further assistance or questions, please contact our support team.