Issues filtering by labels to be excluded

[Alyse Dunn]

I need to construct a JQL query that will enable me to filter out issues that have once particular label. Issues may have multiple labels, including the one that I want to EXCLUDE. I have reviewed the guidance in https://answers.atlassian.com/questions/116842, https://answers.atlassian.com/questions/32014600, and https://answers.atlassian.com/questions/38855712, and unfortunately, none of the tips in these articles have worked for me. Instead, I still see issues that have this label. Additionally, I have tried using the 'Does not contain' logic detailed at this link, https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-operators-reference-764478341.html, but apparently, this logic is not supported with the 'labels' field. As such, could you please advise? If helpful, here is the actual query that I constructed that does NOT appropriately exclude tickets with the label "HideFromBacklog": 

project in ("Platform Infrastructure", Auth) OR assignee in (jnapiorkowski, lbell, kklipsch, dknaack) AND project = "Platform API" OR project = "Product Requirements" AND "Tribe/s" = "platform infrastructure" AND status in ("In Development", "Partial Release", Released) AND (labels != HideFromBacklog OR labels is EMPTY) ORDER BY updated DESC, "Rank (Obsolete)"

Thank you,

Alyse

[Peter Geshev · Accepted solution]

Try 

(project in ("Platform Infrastructure", Auth) OR assignee in (jnapiorkowski, lbell, kklipsch, dknaack) AND project = "Platform API" OR project = "Product Requirements" AND "Tribe/s" = "platform infrastructure" AND status in ("In Development", "Partial Release", Released)) AND (labels != 'HideFromBacklog' OR labels is EMPTY) ORDER BY updated DESC, "Rank (Obsolete)"

[Nicolas Bourdages]

Your problem might be in the priority of AND/OR operations. The way your query is constructed, the labels restriction only applies to the Product Requirements project, because the statement between AND operators are resolved first. If issues from other projects have the label you want to ignore, they will show up in your results.

Try to isolate the labels statement using parentheses. Something like this:

(project in ("Platform Infrastructure", Auth) OR assignee in (jnapiorkowski, lbell, kklipsch, dknaack) AND project = "Platform API" OR project = "Product Requirements" AND "Tribe/s" = "platform infrastructure" AND status in ("In Development", "Partial Release", Released)) AND (labels != HideFromBacklog OR labels is EMPTY) ORDER BY updated DESC, "Rank (Obsolete)"

[Tara Edwards]

THIS!!  Finally solved my problem.  Thank you SO much. 

[Pablo Beltran]

SQL for JIRA is a way that you might definitively want to explore. What it makes different to the rest is:

1. the ability to create your own JQL functions avoiding the opacity brought by most plugins
2.  the flexibility you might require in a future to refine your custom JQLs to match new and more sophisticated requirements.

The major steps are:

1. Remove the label condition from your JQL in the Issue navigator Advanced search.
2. Export your JQL to the SQL. This will bring up the built-in SQL console.
3. Modify the default SQL to meet your requirements: mostly, outer join the ISSUES and the ISSUECOMMENTS tables. (outer join to include issues with no comments).
4. it is standard SQL so you are able to write the most sophisticated or simplest SQL depending of your abilities for that,
5. Once your SQL shows the expected issues, transform it into a JQL again by using the sql built-in JQL function.