It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Using JQL queries in ScriptRunner

Good morning, 

I'm trying to run a JQL query with ScriptRunner for Jira, however; my queries have some characters that need to be considered. Basically, the queries are focused on a project and 2 fields. 

Example: 

def query = jqlQueryParser.parseQuery("project = Portfolio and cf[12947] = CareerLink and cf[12946] = 1.2")

When I run the query on a field that has "( ) " in it, the result is "[]" 

Example:  

def query = jqlQueryParser.parseQuery("project = Portfolio and cf[12947] = WebSites (Core) and cf[12946] = 1.2")

 

Or, when I have a field that has a "-" in it, I get an error.

Example: def query = jqlQueryParser.parseQuery("project = Portfolio and cf[12947] = Cognos BI - Infrastructure and cf[12946] = 2.6")

Result: 

jql result.png

Any suggestions? 

 

Thank you all in advance!

2 answers

In JIRA 8.x use SearchService instead of SearchProvider

import com.atlassian.jira.bc.issue.search.SearchService 

...
def searchService = ComponentAccessor.getComponent(SearchService.class)

....
def results = searchService.search(user, query, PagerFilter.getUnlimitedFilter())

results.getResults().each {issue ->
def issue_res = issueManager.getIssueObject(issue.id)
....

How can you set date in script and use in query?

1 vote

Hello,

Your code should be like this:

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.jql.parser.JqlQueryParser;
import com.atlassian.jira.issue.search.SearchProvider;
import com.atlassian.jira.web.bean.PagerFilter;
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.config.FieldConfig
import com.atlassian.jira.issue.context.IssueContextImpl
import com.atlassian.jira.issue.customfields.manager.OptionsManager
import com.atlassian.jira.issue.customfields.option.Options
import com.atlassian.jira.issue.customfields.option.Option
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder


def findIssues(String jqlQuery) {
def issueManager = ComponentAccessor.issueManager
def user = ComponentAccessor.jiraAuthenticationContext.user
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser.class)
def searchProvider = ComponentAccessor.getComponent(SearchProvider.class)
def query = jqlQueryParser.parseQuery(jqlQuery)
def results = searchProvider.search(query, user, PagerFilter.unlimitedFilter)
results.issues.collect {
issue -> issueManager.getIssueObject(issue.id)
}
}
def user = ComponentAccessor.jiraAuthenticationContext.userCustomField
cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Product Name+")
CustomField cf1 = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Product Name+ 2.0")
def jqlQuery = "project = Portfolio and cf[12947] = \"Cognos BI - Infrastructure and cf[12946] = 2.6\""
def issues = findIssues(jqlQuery)

Thank you. Next question. If my intent is to populate the query dynamically with values from the custom fields. Let's say I have:

def project = "Cognos BI - Infrastructure"
def version = "2.6"

Using this for test purposes only. What would the query look like?

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.jql.parser.JqlQueryParser;
import com.atlassian.jira.issue.search.SearchProvider;
import com.atlassian.jira.web.bean.PagerFilter;
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.config.FieldConfig
import com.atlassian.jira.issue.context.IssueContextImpl
import com.atlassian.jira.issue.customfields.manager.OptionsManager
import com.atlassian.jira.issue.customfields.option.Options
import com.atlassian.jira.issue.customfields.option.Option
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder


def findIssues(String jqlQuery) {
def issueManager = ComponentAccessor.issueManager
def user = ComponentAccessor.jiraAuthenticationContext.user
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser.class)
def searchProvider = ComponentAccessor.getComponent(SearchProvider.class)
def query = jqlQueryParser.parseQuery(jqlQuery)
def results = searchProvider.search(query, user, PagerFilter.unlimitedFilter)
results.issues.collect {
issue -> issueManager.getIssueObject(issue.id)
}
}
def user = ComponentAccessor.jiraAuthenticationContext.userCustomField
cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Product Name+")
CustomField cf1 = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Product Name+ 2.0")
def jqlQuery = "project = ${issue.getProject()} and cf[12947] = \"Cognos BI - Infrastructure\" and cf[12946] = ${issue.getFixVersions()}"
def issues = findIssues(jqlQuery)

My apologies @Alexey Matveev [cPrime], I should have provided a bit more context. What I am doing is using a single project, "Portfolio", where the issue represent other projects and versions. The reason for this is the my management is tracking fund codes. So, What I am doing is setting up some behaviors where given a certain set of values, which are pulled from the 2 custom fields, "Project Name" and "Project Version".

Therefore I need to be able to add the values from those fields into the query. So cf[12947] is "Project" and needs to pull the value from project variable and cf[12946] is "Project Version" and needs to pull the value from version variable.

def jqlQuery = "project = Portfolio and cf[12947] = project variable and cf[12946] = version variable")

I was using def project = "Cognos BI - Infrastructure" and def version = "2.6" so that I could test the script where the values were inserted into the query.

I'm very sorry for any confusion and very much appreciate your assistance.

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.jql.parser.JqlQueryParser;
import com.atlassian.jira.issue.search.SearchProvider;
import com.atlassian.jira.web.bean.PagerFilter;
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.config.FieldConfig
import com.atlassian.jira.issue.context.IssueContextImpl
import com.atlassian.jira.issue.customfields.manager.OptionsManager
import com.atlassian.jira.issue.customfields.option.Options
import com.atlassian.jira.issue.customfields.option.Option
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder


def findIssues(String jqlQuery) {
def issueManager = ComponentAccessor.issueManager
def user = ComponentAccessor.jiraAuthenticationContext.user
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser.class)
def searchProvider = ComponentAccessor.getComponent(SearchProvider.class)
def query = jqlQueryParser.parseQuery(jqlQuery)
def results = searchProvider.search(query, user, PagerFilter.unlimitedFilter)
results.issues.collect {
issue -> issueManager.getIssueObject(issue.id)
}
}
def user = ComponentAccessor.jiraAuthenticationContext.userCustomField
cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Project Name")
CustomField cf1 = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Project Version")
def jqlQuery = "project = ${issue.getProject()} and cf[12947] = ${issue.getCustomFieldValue(cf1)} and cf[12946] = ${issue.getCustomFieldValue(cf2)}"
def issues = findIssues(jqlQuery)

Retracting previous message.

How can you format query to work with date? ex: created > 2019-07-01. "-" doesn't seem to work

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Jira

The add-in you’ve been waiting for: Jira Cloud for Excel 🙌

Introducing Jira Cloud for Excel Here at the product integrations team at Atlassian, we are thrilled to announce the new Jira Cloud for Excel add-in! This add-in lets you export Jira data directly ...

880 views 7 24
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you