It is possible to search for specific values within a database field using JQL. The search can be done in two ways: using dbcfExpr JQL function or using issue.property syntax.
The following Advanced Row Field will be used as example for each of the two search options:
dbcfExpr JQL function
The dbcfExpr JQL function is available starting with version 6.1.820.2 and it requires only one argument: a valid JQL string. This JQL string is allowed to refer to any Advanced Database Row Field by its name.
Here is how you can use it to search for issues:
key in dbcfExpr("Country ~ Spain")
If a custom field name (or value) contains spaces (or any special character), please enclose it in single quotes. For a field named, My Country with value Trinidad and Tobago the query would look like:
key in dbcfExpr("'My Country' ~ 'Trinidad and Tobago'")
If you only want to search for a certain column in the Advanced Database Row Field, you can, using the following syntax:
key in dbcfExpr("Country.code ~ ES")
When you do not specify a column name the search is done on all columns.
Starting with version 6.2.820.3, to respond to some performance issue that were reported, the dbcfExpr function has an extra parameter that represents the maximum number of results to be shown:
key in dbcfExpr("Country ~ Spain", maxResults)
maxResults is optional and it needs to be an integer value. If it is missing, the number of results is limited to a default value of 1000.
It is highly recommended that if you need to apply additional filters to narrow down the search results, to do it inside the dbcfExpr condition, like this:
key in dbcfExpr("Country ~ Spain and project = DemoProject")
issue.property syntax
The complete syntax to search for issues using this method:
issue.property["dbcf"].customfield_10300_name ~ Spain
This method only works using custom field id, not name or alias.
To see how this function works, lets break it apart:
Prefix - This part of the function is static. This syntax should never change:
issue.property["dbcf"]
Field and label - The next part of the function is the id of the custom field that is getting searched (Ids will vary across instances. To look up the id of a custom field use the Custom Field Usage tool). After the underscore is the label, this corresponds to the column of the database field.
customfield_10300_name
Operator and value - It is important to ensure that you are using the proper operator type.
~ Spain
String - When searching strings it is recommended to use “~” and “!~” (contains and does not contain) instead of “=” and “!=” to get the appropriate results.
Numbers and Dates - When searching the normal numerical operators will work, such as “=, !=, <, >
More Examples
Search for issues missing a value for country:
key in dbcfExpr("Country is empty")Search for countries having gdp_capita greater than 20000:
key in dbcfExpr("Country.gdp_capita > 20000")Searching for a country by name and code:
key in dbcfExpr("Country.name ~ Spain and Country.code ~ ES")