How to perform Jql with Join query in Jira cloud?

[Maor Avitan]

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

[Andy Heinzer]

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