JQL for filter by custom field value

[hbhardwaj3]

I have custom field as text values. I want to create a dashboard through filters and I want to filter data according to custom field value.

[Deleted user · Accepted solution]

Hi,

I've once copied some information for JQL, but I don't remember from where. Anyways, here is some info about searching for values on a text field.

Kind regards
Jorden

1. CONTAINS: ~

   The "~" operator is used to search for issues where the value of the specified field matches the specified value (either an exact match or a "fuzzy" match — see examples below). For use with text fields only, i.e.:
   Summary
   Description
   Environment
   Comments
   custom fields which use the "Free Text Searcher"; this includes custom fields of the following built-in Custom Field Types
   1. Free Text Field (unlimited text)
   2. Text Field (< 255 characters)
   3. Read-only Text Field

   The JQL field "text" as in text ~ "some words" searches an issue's Summary, Description, Environment, Comments. It also searches all text custom fields. If you have many text custom fields you can improve performance of your queries by searching on specific fields, e.g. 
   Summary ~ "some words" OR Description ~ "some words"
   Note: when using the "~" operator, the value on the right-hand side of the operator can be specified using JIRA text-search syntax.
   EXAMPLES
   Find all issues where the Summary contains the word "win" (or simple derivatives of that word, such as "wins"):

   summary ~ win

   Find all issues where the Summary contains a wild-card match for the word "win":

   summary ~ "win*"

   Find all issues where the Summary contains the word "issue" and the word "collector":

   summary ~ "issue collector"

   Find all issues where the Summary contains the exact phrase "full screen" (see Reserved Characters for details on how to escape quote-marks and other special characters):

   summary ~ "\"full screen\""

2. DOES NOT CONTAIN: !~

   The "!~" operator is used to search for issues where the value of the specified field is not a "fuzzy" match for the specified value. For use with text fields only, i.e.:
   Summary
   Description
   Environment
   Comments
   custom fields which use the "Free Text Searcher"; this includes custom fields of the following built-in Custom Field Types
   1. Free Text Field (unlimited text)
   2. Text Field (< 255 characters)
   3. Read-only Text Field

   The JQL field "text" as in text ~ "some words" searches an issue's Summary, Description, Environment, Comments. It also searches all text custom fields. If you have many text custom fields you can improve performance of your queries by searching on specific fields, e.g. 
   Summary ~ "some words" OR Description ~ "some words"
   Note: when using the "!~" operator, the value on the right-hand side of the operator can be specified using JIRA text-search syntax.
   EXAMPLES
   Find all issues where the Summary does not contain the word "run" (or derivatives of that word, such as "running" or "ran"):

   summary !~ run

[hbhardwaj3]

Thanks @[deleted]  , custom field have values like "D" "D-1" D-2" "D+1" D+2"

I just want to fetch list of issue based on these values. I already tried ~ but it gave result of all values since all values contain D.

[Deleted user]

Mm, that's a tricky one. You may be able to achieve this by combing both the "~" and the "!~" operators. (e.g.: customfield ~ "D" AND customfield !~ "D-1" ....)

Although this won't be a clean JQL. Querying on text fields is pretty limited. Maybe it would be more helpful to use labels for this kind of information, if that suits your setup of course?

[hbhardwaj3]

Let me try it, I will let you know if i got the desired result.

Thank you for the help Jorden!

[Mobily Monks]

@hbhardwaj3 

I would advise for faster and exact result, you could try using JQL Extensions plugin  fieldMatch keyword. 

It should certainly help. 

We also had similar use-cases demands from management where ScriptRunner plugin JQL could not help and apparently purchased and start using JQL Search Extensions.

For example - 

Using JQL Search Extensions 'Subqueries' tab from the menu, I have written a query to fetch ALL issues having the regular expression 'Auto*'  based on value stored in custom field  'Client Name' 

Result :

As you could see, based on regular expressions I have put in, matching custom field issue in result is only one.

You could refer JQL extensions plugin doc  to know more.

[hbhardwaj3]

Thank you @Mobily Monks  I will try this plugin. 

[Marc Isikoff]

Sorry but some companies disallow plugins - but plugins are what Jira functionality should have - another bad strategy by Atlassian.