JIRA update custom field on issue using PostgreSQL data

This article assumes you are using JIRA cloud and know how to create and manage JIRA issues.

We will use Metamug API Console to update a custom field on a JIRA issue using the JIRA cloud REST API and data from PostgreSQL database. This example is valid for any other SQL database.

Add custom fields to JIRA issue

We will start by adding two custom text fields Event Number and Event Description to a JIRA issue. This can be done by opening a JIRA issue and opening the dropdown menu on the top right corner.

jira add custom field

Select Configure from the Menu.
In the next screen, select Short Text from the right hand side and put the field name as Event Number.
Click on Save Changes. You will now see your new custom field appear on your JIRA issues
Repeat the same procedure for another custom field Event Description.

After creating the custom fields you need to find out your custom field IDs. You will need your custom field IDs in order to retrieve and update the fields using the JIRA REST API. In this example let's assume you have the following IDs

Custom Field Name	Custom Field ID
Event Number	`customfield_10049`
Event Description	`customfield_10051`

Create database table

Let us create a simple database table here that stores event numbers against event descriptions.

CREATE TABLE event (
    event_id BIGSERIAL NOT NULL PRIMARY KEY,
    event_name VARCHAR(64) NOT NULL UNIQUE,
    event_desc VARCHAR(1024) NOT NULL,
    created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Workflow

We will create the following workflow using Metamug console

Retrieve Event ID of a given JIRA issue
Look up description for the event in the database
Update the Event Description field for the given Issue

Create backend and resources

Create a backend in Metamug API console and add your PostgreSQL datasource connection to the backend.

Add JIRA API key as properties

On the backend screen, add your JIRA API key as a backend property. This property will be used in the resource XML for making JIRA REST API call.

JIRA API Key property

Create resource file

Create a resource file with name "customfield" and paste the following XML inside it.

<Resource xmlns="http://xml.metamug.net/resource/1.0" v="1.0">
    <Request method="POST">
        <Text output="true" id="Issue-Key"> $issueKey </Text>

        <XRequest id="issue" method="GET" output="false" url="https://{your_jira_domain}/rest/api/3/issue/$issueKey">
          <Header name="Authorization" value="Basic {{jiraAPIKey}}"/>
        </XRequest>

        <Sql id="eventData" output="true" datasource="postgresql">
              SELECT * FROM event where event_number=$[issue].fields.customfield_10049
        </Sql>

        <XRequest id="issueUpdate" method="PUT" output="true" url="https://{your_jira_domain}/rest/api/3/issue/$issueKey">
            <Header name="Authorization" value="Basic {{jiraAPIKey}}"/>
            <Header name="Content-Type" value="application/json"/>
            <Body>
                  {
                    "fields": {
                        "customfield_10051" : "$[eventData][0].event_description"
                    }
                }
            </Body>
        </XRequest>
      </Request>
</Resource>

The above XML represents a POST request. A POST request made to {yourbackendname}/v1.0/customfield with request parameter issueKey={jiraIssueKey} will fetch the event ID value for the given issue, look up the event description in the connected database and update the event description value on the JIRA issue.

We have called JIRA REST API using XRequest and used Sql for querying our database.