Skip to main content
Skip table of contents

Field query configuration



Definition

The "Query" part of a field configuration is where you set how Elements Connect shall retrieve and store field options and values.

We can split datasources into two categories, depending on the format of the result set: 

  • two-dimensional result set
  • tree structure result

Two-dimensional result sets

The data returned by these datasources can be formatted in a two dimensional table, where:

  • table headers are the name of each attributes
  • table rows are the options returned (1 row = 1 option) 

Datasources returning two-dimensional dataset: 

  • Jira issues (JQL)
  • Jira database
  • Database
  • JNDI
  • LDAP
  • Salesforce
  • URL (CSV format)
  • File (CSV format)

The configuration is made in 2 steps:

  • Query
  • Key

Tree structure result sets

The data returned by these datasources are formatted in a tree.

Datasources returning result set in a Tree structure: 

  • URL (JSON & XML format)
  • File (JSON & XML format)

The configuration requires two additional steps compared to two-dimensional structures:

  • Root element
  • Columns



Query a datasource returning a two-dimensional result set

Query

The first step it to write the Query.  This is how Elements Connect retrieves the full list of options from the datasource.




Depending on the datasource type, you'll shall use a specific query language:

Datasource typeQuery language
Jira IssuesJira Query Language (JQL)
Jira databaseSQL
DatabaseSQL
JNDISQL
LDAPLDAP
SalesforceSalesforce Object Query Language (SOQL)
CSV

When using a URL or File datasource, it's not possible to query a CSV file.

However, you can use a JDBC driver to run SQL queries on a CSV file.

The output of this step is a result set formatted in a table, let's take an example:

Query

SQL
SELECT id, summary
FROM jiraissue
ORDER BY summary

Result set

idsummary
12143"Could not start deployment as the server is paused" logged on API call to dashboard
11499"Force stop build" check box needs to be un-checked twice (only for the first time)
11877"Log in as User" feature in Server
11410"Operation not permitted" failure to clean up Docker runner build directory
12079"Show More Plans" button not obvious enough
12006"Test" action on SMTP Mail Server settings will persist data
11391"Unknown" reason column for customised builds when using import zip
11495(Documentation) Bamboo upgrade guide should cover Oracle database upgrade steps
11734(Documentation) Describe which configuration is preserved when cloning a plan
11673(Documentation) REST API Permission missing request body

You can make the query dynamic by using the Velocity functions and conditions

Read more about Velocity in Elements Connect.

Key

The key is where you tell Elements Connect what should be stored in Jira database.

It must be a unique identifier of each row of the result set, this value is later used when an Elements Connect field is displayed or searched.

It's a numerical value representing the index of the attribute in the result set, we use a zero-based numbering starting at 0.





Live fields only

This settings is only available when configuring a Live field. Regarding Snapshot fields, Elements Connect stores the display value in Jira database.

Selecting a good key can be tricky. If needed, the following articles may be helpful:

Query a datasource returning a tree-structured result set

Query

In the query, you can set the path to the external resource.

Depending on the datasource type, it can be a file path (File datasource) or an URL path (URL datasources).

The result of this step is a file, in XML or JSON format.

Example

Query

CODE
api/2/search

(this is the path to the Search REST API endpoint of Jira)

Result

JS
{
  "issues": [
    {
      "id": "12159",
      "key": "TEST-964",
      "fields": {
        "summary": "Provide Dedicate Agent information under PROJECT, PLAN, JOB, DEPLOYMENT, ENVIRONMENT",
        "created": "2019-06-24T14:59:32.000+0000"
        }
      }
    },
    {
      "id": "12158",
      "key": "TEST-963",
      "fields": {
        "summary": "Shared artifact built on remote agent does not preserve permissions with a Windows Bamboo Server",
        "created": "2019-06-24T14:59:32.000+0000"
      }
    },
    {
      "id": "12157",
      "key": "TEST-962",
      "fields": {
        "summary": "Restricted admin have no access to deployment projects that have no project permission granted",
        "created": "2019-06-24T14:59:32.000+0000"
      }
    },
    {
      "id": "12156",
      "key": "TEST-961",
      "fields": {
        "summary": "shortPlanName contains branch name for plan branches",
        "created": "2019-06-24T14:59:32.000+0000"
      }
    }
  ]
}

Root element

Now that we get a file, we have to tell Elements Connect how to process it.

The first step it to configure the root element of the result set, the location in file where are located all the options.

In the document above, options are located under "issues" attribute of the JSON file. We use JSON Path to set the root element in order to get an array of issue elements. Our root element is:

CODE
$.issues.*

The output of this step is a set of document, one per element located under the root path element.

Example

If we continue the example shown above, we get:

Document 1

CODE
{
  "id": "12159",
  "key": "TEST-964",
  "fields": {
    "summary": "Provide Dedicate Agent information under PROJECT, PLAN, JOB, DEPLOYMENT, ENVIRONMENT",
    "created": "2019-06-24T14:59:32.000+0000"
    }
  }
}

Document 2

CODE
{
  "id": "12158",
  "key": "TEST-963",
  "fields": {
    "summary": "Shared artifact built on remote agent does not preserve permissions with a Windows Bamboo Server",
    "created": "2019-06-24T14:59:32.000+0000"
  }
}

Document 3

CODE
{
  "id": "12157",
  "key": "TEST-962",
  "fields": {
    "summary": "Restricted admin have no access to deployment projects that have no project permission granted",
    "created": "2019-06-24T14:59:32.000+0000"
  }
}

Document 4

CODE
{
  "id": "12156",
  "key": "TEST-961",
  "fields": {
    "summary": "shortPlanName contains branch name for plan branches",
    "created": "2019-06-24T14:59:32.000+0000"
  }
}

Columns

Each document returned by the previous step will be a field option.

The last step is to convert this tree-structured document in a two dimensional table.

You can do this by setting the Columns.
A column has a name (as it will be used in the display template) and a path in JSON Path.

Example

In the example we have seen previously, the columns would be:

Columns

namejson path
id$.id 
key$.key 
summary$.fields.summary 

Result

idkeysummary
12159TEST-964Provide Dedicate Agent information under PROJECT, PLAN, JOB, DEPLOYMENT, ENVIRONMENT
12158TEST-963Shared artifact built on remote agent does not preserve permissions with a Windows Bamboo Server
12157TEST-962Restricted admin have no access to deployment projects that have no project permission granted
12156TEST-961shortPlanName contains branch name for plan branches

This table result set is then used to generate the option list in the field.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.