Create
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Products
See all
Community resources
Top groups
Explore all groups
Community resources
Learn
Community resources
Events
Community resources

Using JQL queries in ScriptRunner

Thomas Hardin
Thomas Hardin June 20, 2018

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!

Answer
Watch
Like # people like this
11032 views

2 answers

8 votes
Andrey Minov
Andrey Minov September 13, 2019

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)
....
View More Comments
Eric Monfette
Eric Monfette October 22, 2019

hmm

Like
Eric Monfette
Eric Monfette October 23, 2019

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

Like
laralg
laralg
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
November 14, 2019

@Andrey Minov You just helped me fix a bunch of scripts! Only answer in the whole internet showing this properly!! <3

Like • # people like this
Reply
1 vote
Alexey Matveev
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
June 20, 2018

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)
View More Comments
Thomas Hardin
Thomas Hardin June 20, 2018

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?

Like
Alexey Matveev
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
June 20, 2018 
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)
Like
Thomas Hardin
Thomas Hardin June 20, 2018

My apologies @Alexey Matveev, 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.

Like
Alexey Matveev
Alexey Matveev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
June 20, 2018 
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)
Like
Edward Greathouse
Edward Greathouse January 9, 2019

Retracting previous message.

Like
Eric Monfette
Eric Monfette October 22, 2019

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

Like
Reply

Suggest an answer

Log in or Sign up to answer
Jira
Jira Software
TAGS
AUG Leaders

Atlassian Community Events

    See all events