Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,365,056
Community Members
 
Community Events
168
Community Groups

How to get values from database picker field via REST API?

Hi,

Is it possible to get value names from the database picker field via REST call?

If just try to call the custom field via GET method, you will get a list/array of IDs of each selected item in the picker field. Now what I need to get is names (labels) for each value stored in this DB picker field.

Is this something that's possible to get via REST call?
I'm aware that you can use the script stated on the official docs page but that's not what I'm looking for.

1 answer

1 accepted

Hi @Tomislav Tobijas

In your description, you asked:-

Is it possible to get value names from the database picker field via REST call?

Are you referring to the value that is displayed in the DB Picker?

If yes, you could try doing this with the Server-Side Behaviour for the DB Picker field, i.e. for example:-

import com.onresolve.jira.groovy.user.FieldBehaviours
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.transform.BaseScript

@BaseScript FieldBehaviours behaviours

def dbPicker = getFieldById(fieldChanged)
def dbPickerValue = dbPicker.value.toString()

def dbResultValue = new StringBuilder()

def output = []
def result = [:]

if (dbPicker) {

DatabaseUtil.withSql('local_db') { sql ->
if(dbPicker.value != null) {
output = sql.rows("select name from sample_table where id = ?", Integer.parseInt(dbPickerValue))
}
}

output.findAll {
result = it as Map<Integer,String>
}

result.values().findAll {
dbResultValue.append(it.toString())
}
}

log.warn "========>>>> ${dbResultValue.toString()}"

Please note that the sample code above is not 100% exact to your environment. Hence, you will need to make the required modifications.

Below is a screenshot of the DB Picker's Server-Side Behaviour configuration:-

db_picker_config.png

The variable local_db that the DatabaseUser object uses refers to the local database configuration that is setup using ScriptRunner's Resources configuration, as shown in the image below:-

image2.png

Below is a screenshot of the DB Picker configuration that I have tested with:-

db_picker_config_2.png 

In the example above, I am trying to query a table in the database and pass the value selected in the database picker, i.e. the ID, to get the value displayed on Database Picker.

Once the value is obtained, it is stored in a StringBuilder, which is used to set the value in the Summary field.

Below is the sample table that I am testing:-

 ID  Name
 1  Malaysia
 2  India
 3  Singapore
 4 Brunei
5 Sri Lanka
6 Thailand
7 Indonesia
8 Japan
9 Taiwan

Below are some sample test screens for your reference:-

1) When the issue is first created, the DB Picker value is not set as shown below:-

image1.png

2) Once the option is selected from the DB Picker, as shown below, the value will be printed in the log as shown in the second screenshot below:-

image2.png

image3.png

3) If the DB Picker option is updated as shown below:-

option2.png

the log output will also update accordingly, as shown below:-

output.png

I hope this helps to solve your question. :)

Thank you and Kind regards,

Ram

Hi Ram,

Firstly, thank you for the detailed explanation and instructions!

If I got this right, what you're doing with this behavior is to visualize the name of the DB object inside the DB Picker field. Please correct me if I'm wrong.

I'm quite new to the REST calls and responses so it might be the case that I've misinterpreted your answer.

So, what I would need is to get the names of selected objects (after they have been updated in the DB Picker field), as when I call Jira to give me an issue response, it provides only a list of DB object IDs where I would need to get/show names.

In the example below, if you look at customfield_13010 which is a multi-choice DB picker field, values stored inside are IDs of selected objects (e.g. "11", "14"...) where I would want something like "Name_1", "Name_2"...

2022-08-16_15-18-54.png

Is this something that can be achieved by using the GET method?

Best regards,
Tomislav

Hi @Tomislav Tobijas

In your previous comment, you asked:-

If I got this right, what you're doing with this behavior is to visualize the name of the DB object inside the DB Picker field. Please correct me if I'm wrong.

Not actually. What I am doing is I am getting the value displayed in the DB Picker field by querying it on the Database Using the DB Picker's ID as the reference value.

If you try to log the DB Picker's field value, the only result it will return is the DB Picker's ID.

Pretty much the same as the result that you get from the REST Endpoint.

Even if you use the REST Endpoint, you will still need to pass the value that you have obtained and query it on the Database to get the value selected from the DB Picker.

If you still want to use the REST Endpoint, may I know how you want to invoke the result returned by the REST Endpoint? Are you using a Post-Function, Script Console or Behaviour? I am asking this so that I can provide a sample code.

Thank you and Kind regards,

Ram

Hi @Ram Kumar Aravindakshan _Adaptavist_ and thanks for the fast response!

So, this was actually a client's request. I'm not sure how they are pulling data from Jira - is it periodically or each time a ticket/issue is created. Based on that, I'm not sure what would be the best approach here.

I can say that I'm almost certain that they are not using Behaviors for it, and in this context, Script Console is not needed.

So, looking at this, if it isn't possible to get values (object names) from a REST Endpoint, maybe a good solution would be to store that string containing object names in the new text field, which then the client can get by using the GET method from REST? So maybe Behavior which stores that text string (can be a list of strings as well) in a new field each time the DB Picker field is updated would be a good idea here?

I'm really still just exploring what's possible in here.

Thanks!

Tom

Hi @Tomislav Tobijas

From your response, if it is something that is to be executed periodically, the Jobs would be the best approach.

I will try to provide an example get back to you.

Thank you and Kind regards,

Ram

Like Tomislav Tobijas likes this

Hi @Tomislav Tobijas

Below is the sample code for the REST Endpoint:-

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.HttpResponseDecorator
import groovyx.net.http.RESTClient

import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

@BaseScript CustomEndpointDelegate delegate
getDBPickerValue { MultivaluedMap queryParams ->
def issueKey = queryParams.getFirst('issueKey')

def applicationProperties = ComponentAccessor.applicationProperties

final def baseUrl = applicationProperties.getString('jira.baseurl')
final def username = 'admin'
final def password = 'q'
final def headers = ['Authorization': "Basic ${"${username}:${password}".bytes.encodeBase64()}", 'Accept': 'application/json'] as Map

def http = new RESTClient(baseUrl)
http.setHeaders(headers)

def resp = http.get(path: "/rest/api/2/issue/${issueKey}") as HttpResponseDecorator

if (resp.status != 200) {
log.warn 'Commander did not respond with 200 for retrieving project list'
}

def issueJson = resp.data as Map

def dbPickerValue = issueJson['fields']['customfield_10800'] //Custom Field ID for DB Picker

Response.ok(new JsonBuilder(dbPickerValue).toPrettyString()).build()
}

Below is a screenshot of the REST Endpoint configuration:-

rest_endpiont_config.png

And below is the sample code for the Escalation Service in Jobs:-

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.json.JsonSlurper

def key = issue.key

def applicationProperties = ComponentAccessor.applicationProperties

final def host = applicationProperties.getString('jira.baseurl')
final def restEndpointName = 'getDBPickerValue'

def baseUrl = "${host}/rest/scriptrunner/latest/custom/${restEndpointName}?issueKey=${key}"
def output = baseUrl.toURL().text

def dbPickerValue = new JsonSlurper().parseText(output) as Integer

def dbResultValue = new StringBuilder()

def result = []

DatabaseUtil.withSql('local_db') { sql ->
if(dbPickerValue) {
result = sql.rows("select name from sample_table where id = ?", dbPickerValue)
}
}

result.collectEntries {
it
}.values().findAll {
dbResultValue.append(it.toString())
}

log.warn "===>>>> ${key} ${dbResultValue.toString()}"

Below is a screenshot of the Escalation Configuration:-

jobs_config.png

Please note that the sample code provided is not 100% exact to your environment. Hence, you will need to make the required modifications.

The REST Endpoint queries the issues by the issue key and extracts the value from the DB Picker field. The DB Picker field id is used to filter the REST result as shown in the code, i.e.:-

def dbPickerValue = issueJson['fields']['customfield_10800'] //Custom Field ID for DB Picker

In my environment, the id for the DB Picker field is 10800; hence it is filtered with customfield_10800. You will need to modify this accordingly.

Once the result is filtered, the ID of the DB Picker will be returned by the REST Endpoint.

Next, the Escalation Service is used to query the REST Endpoint and extract its value.

The value returned, i.e. the field's ID, will be added to the SQL Query to get the field's name as shown in:-

def dbPickerValue = new JsonSlurper().parseText(output) as Integer

def dbResultValue = new StringBuilder()

def result = []

DatabaseUtil.withSql('local_db') { sql ->
if(dbPickerValue) {
result = sql.rows("select name from sample_table where id = ?", dbPickerValue)
}
}

result.collectEntries {
it
}.values().findAll {
dbResultValue.append(it.toString())
}

log.warn "===>>>> ${key} ${dbResultValue.toString()}"

The query result is filtered, and the DB Picker value that has been selected for the issue is returned in the log as shown in the image below:-

log_output.png

I hope this helps to answer your question. :)

Thank you and Kind regards,

Ram

Like # people like this

Perfect @Ram Kumar Aravindakshan _Adaptavist_!

Thank you very much!

We've used part of what you've provided in your answer and modified it a bit. We've decided to create an additional custom text field that will store the string containing all selected object names, separated by a symbol. This was mostly because of the client's current setup and the information that they have provided. We're using Listener to check any changes on an issue and on the DB picker field and then update the textual field accordingly.

In any case, this has helped a lot and it can probably be used at some point in the future as well, so all these detailed answers are much appreciated!

Best regards,
Tom

Hi @Tomislav Tobijas

Great to hear the solution worked.

Please also accept the answer.

Thank you and Kind regards,

Ram

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events