Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

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

How to write a nested query

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. 

4 answers

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:

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

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 ) 

0 votes

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

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.

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/

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

🎨 Add some visual life to your templates

Hi Atlassian Community, My name is Avni Barman, and I am a Product Manager on the Confluence Cloud team. Based on feedback from you, we are giving admins more power to create templates that a...

155 views 1 7
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