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 perform Jql with Join query in Jira cloud?

i try to perform this query:
issuetype = "AI2_Order" and linkedIssueType = "Field" and Crop is Empty.
however, the Crop field is in "AI2 Order" and "Field - Test" i would like to filter only the crop field inside "Field - Test" to be Empty

if it was SQL it was like this:
select * from AI2Order left join  Field - Test on AI2Order.field_id = Field.id where Field.crop  = ""

 

there is any way to perform like "Join" command in JQL but using Jira cloud only?
i tried this extension app: https://marketplace.atlassian.com/apps/1214791/jql-search-extensions-for-jira-reports?hosting=cloud&tab=overview,

but not enough...
 

1 answer

0 votes
Andy_Heinzer Atlassian Team Jan 03, 2020

Hi Maor,

Sorry to report that Jira's JQL does not have a join command or similar similar function.   JQL is designed to only be able to return Jira issues.  While there is some adjustments you might be able to make with the ORDER BY clause in terms of sorting the results, that won't restrict the results of the rest of the JQL query in any way.  JQL simply is not able to do a join in the manner you are describing here.

Further more, Jira Cloud users don't have access to the database itself.   If you were running Jira Server, then of course you could have direct SQL access to the database itself in order to perform SQL joins as you wish there.  But it's not really a trivial task to copy your Cloud data and then migrate to Server.  It is possible, it's just not a simple thing to go about doing in all cases.  More details about it in Migrating from Jira Cloud to Server.

 

Could you tell me more about what you're trying to get back here?  I gather that you're using the linkedissuetype function.  I believe that's a function of the JQL search extensions.  Is it that you're looking to return all the issues of type 'AI2_Order' that are linked to issues of type 'Field', but the crop field is found on the Field type issues?  Is that right?  If so, then perhaps there is another way to come about this.   Perhaps you could use an add-on such as Enhanced Search (JQL & Subqueries). I found some documentation in https://docs.adaptavist.com/enhanced-search/jira/cloud/latest/functions/issue-link-functions.html that seems to indicate you can build a JQL query such as:

issueFunction in linkedIssuesOf("project=xyz AND Crop IS EMPTY", "blocks") AND issuetype=AI2_Order

This query will only return issues of type AI2_Order that have a block linktype to an issue in the xyz project with an empty crop field.

There also might be other add-ons in Marketplace that could help here, but I'm not familiar with all the potential solutions here.

I hope this helps.

Andy

Suggest an answer

Log in or Sign up to answer
TAGS

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