JQL to search multi-user picker values

[Esther Strom]

I have a set of tickets that has a multi-user picker field. Some of the tickets do only have a single value in that field, but many of them have multiple values, as is allowed.

What I'm trying to do is search for tickets that don't have only a specific user as a single value, while not excluding tickets that have that specific user as one of multiple values.

Example: My field is called Contributors. Some tickets have only John Smith in that field; others have John Smith and Mike Brown, or John Smith and Jill Green.

What I'm trying to do is create a search that will exclude all tickets that have only John Smith, while still returning tickets that have John Smith plus at least one other value.

Contributors[User Picker (multiple users)]" not in (John Smith's ID)

eliminates all tickets with John Smith in the Contributors field, even if the field also has other values. Is what I'm trying to do possible?

[David Kintzinger]

Hi,
just in case this might still be relevant here's an idea:

You could try something like this: 

"Contributors[User Picker (multiple users)]" is not EMPTY 
and "Contributors[User Picker (multiple users)]" != (John Smith's ID)

 The != should search for results where only John Smith is contained whereas NOT IN checks if John Smith is part of the returned user set. 

The EMPTY check should be necessary because an EMPTY field would of course satisfy the != JohnSmith condition.

Be aware that these not conditions seem to work extremely odd in Jira though. 
We recently had a situation where we wanted to exclude all tickets that had personA in a field like Contributors. 
So checking 

"Contributors[User Picker (multiple users)]" not in (personA ID)

like you did should do the trick. It didn't though. Instead all tickets with an EMPYT field as well as all tickets where personA was the only user in field contributors were also shown. 
The positive check

"Contributors[User Picker (multiple users)]" in (personA ID)

 worked without flaws though an returned exactly what we expected. 

So just in case there are any troubles, just try around for a bit ;) 

[Somnath Patil]

Hi Esther,

Try this one : 

"Contributors[User Picker (multiple users)]" = John Smith AND "Contributors[User Picker (multiple users)]" in (Mike Brown, Jill Green)

Name all the users inside in operator after Mike Brown and Jill Green which you want along with John Smith.

[Esther Strom]

@Somnath Patil I appreciate your continuing effort, but again - the second user could be any one of 500. Creating an IN clause with 500 user names or IDs is not possible.

[Somnath Patil]

Hi Esther, 

You should try following JQL : 

("Contributors[User Picker (multiple users)]" = John Smith AND "Contributors[User Picker (multiple users)]" Jill Green) OR ("Contributors[User Picker (multiple users)]" = John Smith AND "Contributors[User Picker (multiple users)]" = Mike Brown)

[Esther Strom]

@Somnath Patil thank you for the response. But I may have oversimplified. The "other" users could be any of our 500 users, and creating an OR statement for that is not exactly efficient. What I'm trying to do is find and exclude tickets where a specific user is the ONLY value in the field, while NOT excluding tickets where the specific user is one of multiple (unknown) values.

[Claudio de Lima Sales]

Hello @Esther Strom , did you find a solution? I think if there is a solution to my question it would be useful for your solution.
IIn my case I just want a JQL to check if the MultiUser field has more than 2 users. I haven't found an answer yet, so I'm using JMWE for this solution.

[Esther Strom]

@Claudio de Lima Sales No, I never did find a solution. It was kind of a one-off need (to do some data cleanup) and we ended up just going through all tickets in the project one by one.