It is possible to search for specific values within a database field using JQL.

Image Removed

JQL Search Example

Given a field like the one configured above, if we wanted to find all the issues that have Nissan cars, we can do that using the JQL function seen belowThe 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:

Image Added

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

issue.property syntax

The complete syntax to search for issues using this method:

issue.property["dbcf"].customfield_1110010300_makename ~ Nissan

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.

  Code Block
  ~

• Spain

  1. String - When searching strings it is recommended to use “~” and “!~” (contains and does not contain) instead of “=” and “!=” to get the

• 1. appropriate results.

  2. Numbers and Dates - When searching the normal numerical operators will work, such as “=, !=, <, >

More Examples

1. Search

1. for issues missing a value for country:

   Code Block

issue.property["dbcf"].customfield_11100_year > 2010

Searching for a car by year and make:

issue.property["dbcf"].customfield_11100_year = 2010 AND issue.property["dbcf"].customfield_11100_make ~ Mazda

Search for issues missing a value for model:

issue.property["dbcf"].customfield_11100_model is Empty

Sear for issues with cars made in multiple years:

issue.property["dbcf"].customfield_11100_year in (2010, 2011)

1. key in dbcfExpr("Country is empty")

2. Search for countries having gdp_capita greater than 20000:

   Code Block
   key in dbcfExpr("Country.gdp_capita > 20000")

3. Searching for a country by name and code:

   Code Block
   key in dbcfExpr("Country.name ~ Spain and Country.code ~ ES")