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:

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

Query

SELECT id, summary
FROM jiraissue
ORDER BY summary

Result set

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:

• What is a good key in an Elements Connect field?
• Problem of duplicates in the issue view
• Why is my Connect field not live?

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

• Datasource: Jira REST API
• Root url: https://server:port/rest/

Query

api/2/search

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

Result

{
  "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:

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

{
  "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

{
  "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

{
  "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

{
  "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

Result

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