Searching Advanced Database Row Field values

Owned by Jonathan Muse
Last updated: Nov 19, 2024
3 min read

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.

Please make sure the name of the Advanced Row Field is unique and it doesn’t clash with other fields you might have on the same Jira instance (name case doesn’t matter). Otherwise, searching by field name using dbcfExpr function might not work properly.

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:

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:

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

  • Operator and value - It is important to ensure that you are using the proper operator type.

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

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

More Examples

  1. Search for issues missing a value for country:

  2. Search for countries having gdp_capita greater than 20000:

  3. Searching for a country by name and code: