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 `<#--` and `-->`. They won't get into the output because FreeMarker skips them.	CODE `<#-- This is a comment --> This is not a comment`
Set a variable Use `<#assign>` to define and use variables in your templates.	CODE `<#assign variableName = "value">`
Access a variable Use `${x}` to define and use variables in your templates.	CODE `<#assign variableName = "value"> ${variableName}`
Define conditions Use `#if`, `#elseif`, and `#else` for conditional logic.	CODE `<#if condition> // Code if condition is true <#elseif otherCondition> // Code if otherCondition is true <#else> // Code if no conditions are met </#if>`
Loops Use `<#list>` to iterate over lists.	CODE `<#list list as item> ${item} </#list>`

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 `seq?size != 0` to check if a variable exists and is not null or empty.	CODE `<#assign user = "" /> <#if user?size != 0> /search?jql=order by created DESC`
Join (for sequence)	CODE `<#assign projects = issue.customfield_10074?join(",")> /search?jql=project IN (${projects})`
URL Encoding (for string) Use `${stringVariable?url}` to encode a string for use in a URL.	CODE `<#assign searchTerm = "elements connect"> ${searchTerm?url} <!-- Output: elements%20connect -->`
Check on value (for sequence) Use `${seq?seq_contains("searchedValue")?string("resultIfTrue", "resultIfFalse")}` to check if the sequence contains a specified value	CODE `<#assign x = ["red", 16, "blue", "cyan"]> "blue": ${x?seq_contains("blue")?string("yes", "no")} "yellow": ${x?seq_contains("yellow")?string("yes", "no")} 16: ${x?seq_contains(16)?string("yes", "no")} "16": ${x?seq_contains("16")?string("yes", "no")}`

Some other expressions that might come useful:

Arithmetic Operations Use `+`, `-`, `*`, `/` to make basic arithmetic operations on numeric variables.	CODE `${a * b} <!-- Output: 50 --> ${a / b} <!-- Output: 2 -->`
Logical Operations Just the usual logical operators: Logical or: `\|\|` Logical and: `&&` Logical not: `!` The operators will work with boolean values only. Otherwise an error will abort the template processing.	CODE `<#if x < 12 && color == "green"> We have less than 12 things, and they are green. </#if> <#if !hot> <#-- here hot must be a boolean --> It's not hot. </#if>`
Comparison Use `==` to test two values for equality. To test two values for inequality you use `!=`.	CODE `<#if user == "Big Joe"> It is Big Joe </#if> <#if user != "Big Joe"> It is not Big Joe </#if>`
Concatenation Use `+` to concatenate sequences in the same way as strings	CODE `<#list ["Joe", "Fred"] + ["Julia", "Kate"] as user> - ${user} </#list>`

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"). When using FreeMarker, no need to use '$' to access Connect variables, like issue. Connect variables are available like any FreeMarker variable. What you can retrieve from the issue: `issue.summary` `issue.type` `issue.priority` `issue.assignee.emailAddress` `issue.assignee.accountId` `issue.reporter.emailAddress` `issue.reporter.accountId` `issue.customfield_XXX`	CODE `<#if issue.priority?has_content> /search?jql=priority="${issue.priority}" <#else> /search?jql=priority="Medium" </#if>`
Project variables `project.key` `project.id` `project.name` `project.type` `project.category`	CODE `<#if project.key == "CC"> /search?jql=project=CC <#else> /search?jql=project!=CC </#if>`
Other variables Many other variables can be used in FreeMarker templates. currentUser variables request context variables	CODE `<#if currentUser.emailAddress == "johndoe@example.com"> /search?jql=project IN (SUP, KAN) <#else> /search?jql=project=CS </#if>`
User Input	CODE `<#if userInput?has_content> /search?jql=summary ~ "${userInput}" <#else> /search?jql=ORDER BY created DESC </#if>`
Set/replace a variable Use `<#assign>` to define and use variables in your templates.	CODE `<#assign variableName = "value"> ${variableName}`

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:

CODE

"customfield_10074": [
"PJT1",
"PJT2"
],

Then, the query to retrieve all issues except those related to previously selected projects could be:

CODE

<#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

CODE

<#-- 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.