How to write a nested query

[Sripurna Mitra]

Example : I want to know all stories that belong to say 'x' fixversion, but linked to Epics (thru Epic Link field) that have fixversion 'y'. Essentially , how to find those cases where there is a mismatch of fixversion between stories & their corresponding epics. 

[Earl McCutcheon]

Hello Sripurna,

Natively Jira cannot nest queries for a comparative result set between two issues, we have a feature request for this at the link below, and make sure to add your vote to help track interest:

• https://jira.atlassian.com/browse/JRASERVER-21936

However there are some add-ons that can extend this, offhand I know ScriptRunner has an Scripted JQL function "SubTaskOf()"  that can be used to get a partially nested comparison of subtasks where the parent issues meets a certain criteria with something like the following for a fixversion mismatch query:

fixVersion in ('1.0') and issueFunction in subtasksOf('project = ABC and fixVersion not in ("1.0")')

This would give you all sub tasks in FixVersion 1.0 where the parent is not in 1.0 for project ABC.

For the parent issues save the above filter as filter1 and run the following for anadditioan nested query:

issueFunction in parentsOf("filter=filter1")

And for Epic to story comparison epicsOf can be really useful, and an exe on additional nested use-case would be to set the previous filter saved as filter2 drop that into a filter like:

issueFunction in epicsOf('filter = filter2')

There are other Scripting add-ons and I believe the PowerScripts app has a similar scripts to this one but I do not any examples off hand, and I would recommend checking out their documentation site here.

Regards,
Earl

[Nishant Paraskar]

Nested query are those query which fetch data  from second query and send data to first query as an input  . When  query is run then result will be the  collaborative action of first query and second query (ie sub query ) 

[Emre Toptancı _OBSS_]

Hello @Sripurna Mitra ,

There is this app in marketplace called Search Linked Issues.

This app introduces several new JQL functions and as the name suggests, allows you to make queries using issue links. 

project = ABC and issue in linkedIssueFromQuery(" project = DEF and issuetype = Epic")

For example, this query returns all issues in ABC project that are linked to Epics in DEF project. 

We've been using it for years on our Jira and find it to be very very helpful. Might help with this problem too.

Thanks.

For curious ones: we are not affiliated with them :)  

[Robin Mathew]

Hi Everyone, i have another query realted to nested queries in JIRA.

I have a set of tickets where they have two sets of dates, planned end date and baseline date. Not all tickets have both dates available on them. I need to write a query to pull out all tasks that are have gone past the planned end date or baseline date, but if i write the query using the OR function it gives me results for cases especially when either planned end and baseline end have gone past but the other date maybe a future date.

for example: 

one ticket has the planned date of 31st july 2019 and the baseline end as 30th April 2020. This ticket is past due based on planned date but still on going based on baseline end date.  My current query which gives me a list but i need to exclude cases like the example above where one of the dates is in the future.

My query is as follows: project = "TS pRCSA" AND component = "pRCSA Findings" AND STATUS != Closed AND "Baseline End" <= now() OR (project = "TS pRCSA" AND component = "pRCSA Findings" AND STATUS != Closed AND "Planned End" <= now() 

Can anyone help me how i can update this query?

Thanks.

[Gideon Nolte [Jodocus]]

Hey Robin,

hopefully I'm not totally too late and understood your problem correctly. If I got you right, you'd like to get all issues, for which EXCLUSIVLY either the "Baseline End" or the "Planned End" is past due.

Such behaviour would best be described with an "XOR" operation [1]. (Un)fortunately JQL keywords [2] only provide the "AND", "OR", and "NOT" gates/operations, which we can use to construct an "XOR" operation.

A XOR B = (A OR B) AND (NOT A AND B)
or 
A XOR B = (A and not B) or (not A and B)

As you said, you would like to filter issues based on two fields. Let's say case A describes the "Baseline End" being past due and case B describes the "Planned End" being past due. 

Lets see what the different JQL keywords would mean in those cases:

A and B: Both fields have to be past due.
A or B: One or both fields are allowed to be past due.
A xor B: Exactly one field is allowed to be past due.

Now getting on, to improving your query:

- for clarity, I would suggest to save and use the first part of your query as a filter [3]
- pick your choice of implementing the XOR gate (I picked the first for the solution)

Your final query should then look something like this:

filter = "Closed TS pRCSA Findings" AND (("Baseline End" <= now() or "Planned End" <= now() ) AND NOT ("Baseline End" <= now() and "Planned End" <= now()))

One or both fields are past due, and not both fields are past due. -> One field is past due.

Note: You could also save everything in the parenthesis after the first AND as a filter of its own and concatenate them via AND - this would then look something like this:

filter = "Closed TS pRCSA Findings" AND filter = "XOR - Past due"

Do not use 'filter in ("...", "...")' here as combines the filters via OR.

Hope this helped.

Gideon

[1] https://en.wikipedia.org/wiki/XOR_gate
[2] https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-keywords/
[3] https://support.atlassian.com/jira-core-cloud/docs/save-your-search-as-a-filter/