Using JQL queries in ScriptRunner

[Thomas Hardin]

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: 

Any suggestions? 

Thank you all in advance!

[Andrey Minov]

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

[Eric Monfette]

hmm

[Eric Monfette]

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

[laralg]

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

[Alexey Matveev]

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)

[Thomas Hardin]

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?

[Alexey Matveev]

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)

[Thomas Hardin]

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.

[Alexey Matveev]

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)

[Edward Greathouse]

Retracting previous message.

[Eric Monfette]

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