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

Support for FreeMarker in Custom Fields templates is in our backlog.

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 come from the data model. However, templates can also define their own variables—usually to hold loop counters, temporary results, macros, etc.

Issue variables
Using Connected items

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_XXXXX

Basic check to determine if a field is empty:

CODE

<#if issue.customfield_XXXXX?has_content>
  /project/${issue.customfield_XXXXX}
<#else>
  /project
</#if>

Advanced check, in case the field to be checked is an Object:

CODE

<#if issue?keys?seq_contains("customfield_XXXXX")>
  /project/${issue.customfield_XXXXX.id}
<#else>
  /project
</#if>

⚠️ This advanced check is only relevant when configuring connected items queries. For custom fields, you can only use the simpler has_content() function. See below for more information.

Issue Variables
Using Connect Custom Fields

As with Connected Items, the Issue variable holds data in a JSON-like structure.

However, when you work with Connect custom fields, you can access all variables returned by the JIRA Issue API.

To explore the available data, you can view the JSON content returned by the API by calling https://<instance>.atlassian.net/rest/api/3/issue/<ISSUE-KEY>.

To access a field’s attributes, simply omit the “fields” prefix.

issue.key

issue.id

issue.customfield_12133

issue.statusCategory.name

🤝 Standard Connect variables detailed in this documentation are still available.

If an issue has an ID (i.e., it’s already created), we retrieve the watcher; otherwise, we retrieve the users.

CODE

<#if issue.key?has_content && (issue.watches.watchCount > 0)>
  /issue/${issue.key}/watchers
<#else>
  /users
</#if>

If the issue has components, we retrieve the first one; otherwise, we retrieve all the project components.

CODE

<#if (issue.components?size > 0)>
  /component/${issue.components[0].id}
<#else>
  /project/${issue.project.key}/components
</#if>

If an issue has a fix version, we retrieve the number of issues with that same fix version; otherwise, we retrieve the number of issues using the default fix version ID.

CODE

<#assign fixVersionId="10001">
<#if issue.fixVersions?size != 0>
  <#assign fixVersionId = issue.fixVersions[0].id>
</#if>
/version/${fixVersionId}/relatedIssueCounts

These examples are for illustration only and may not reflect real-world use cases.

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

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.

Additional Resources

Freemarker Official Documentation
For any further assistance or questions, please contact our support team.