Query Jira custom fields

Still need help?

The Atlassian Community is here for you.

Ask the community

In Jira Software and Jira Service Management, users with the proper permissions have the ability to create custom fields in projects. A custom field can be created to collect information that isn’t available in the default system fields.

Custom fields are always optional fields. This means that you can create a new custom field without requiring existing issues to be changed. The existing issues will contain no value for the new custom field, even if a default value is defined.

Not all custom fields are currently supported in the Atlassian Data Lake, such as Marketplace app fields. View open suggestions to watch for updates.

Querying custom fields in Atlassian Analytics

Watch the video below to learn how to query your custom fields, or continue reading the article.

Custom field data can easily be queried in a visual mode query within Atlassian Analytics by using the “Issue custom fields” table. This table is only available to be used in a visual mode query and uses data from the “Issue field” table to turn your custom fields into their own individual columns.

In this example, we’re going to create a chart calculating the number of issues per each value of our custom field named ‘Work category’. We’ll create a chart with the following steps:

1. Open the chart builder by creating a new chart or editing an existing chart.

2. In a visual mode query, expand the “Issue custom fields” table. If your custom field isn’t one of the 10 fields displayed, use the search bar to search for your custom field by its name. Then add your custom field column to the query.

3. Optional: Add extra columns (like “Issue key”, “Labels”, etc.) or additional filters to the query. Since we want to know the count of issues per each ‘Work category', we’ll add the Issue key column from the Issue table under the Jira family of products section.

4. Run the query.

If you would like to have the null row of data excluded from the query to allow the chart to only show the count of issues where a Work category value have been selected, then you can add an additional filter to the query.

5. Optional: Add a query filter where the Work category column from the “Issue custom fields” table is not null.

Now we’re able to see how many issues are in each Work category within our projects.

A pie chart displaying the percentage of issues in each of the 5 work categories.

Querying a custom fields in a “Dropdown” variable control

Querying a custom field within a “Dropdown control” is similar to querying a custom field in a chart.

You’ll need to create your “Dropdown” control with the control editor. Otherwise, you won’t be able to filter by your specific custom field.

1. Add a “Dropdown control” from your dashboard and select Switch to control editor.

2. Expand the “Issue custom fields” table. If your custom field isn’t one of the 10 fields displayed, use the search bar to search for your custom field by its name. Then add your custom field column to the query.

3. Optional: Filter the query where the Work category column from the “Issue custom fields” table is not null. Then run the query.

4. Configure the settings of your “Dropdown” control and then save it to your dashboard.

The Dropdown control is named WORK_CATEGORY. The data type of the control is text and it is configured to be a multi-select control that shows unfiltered chart data when no values are selected.

5. Manually connect your “Dropdown” control to chart(s) on your dashboard using the Work category column from the Issue custom field table and the appropriate filter operator.

Then you’ll be able to filter your chart(s) with your Jira custom field values.

Querying a custom field in SQL mode

To query your custom field through a SQL query, you’ll need to use the “Issue field” table in your query as the “Issue custom fields” table is unavailable in SQL mode queries. The “Issue field” table contains data for both standard Jira fields and custom Jira fields. Custom field data in Atlassian Analytics can be found in the the following columns from the “Issue field” table:

  • Name - This column has the name of the custom field (ie: Change type)

  • Value - This column has the contents of the custom field (ie: for Change type the values would be ‘Standard’, ‘Normal’, ‘Emergency’)

Some commonly used Jira Service Management custom fields (like “Change type” and “Change risk”) are broken out as individual columns within the “Change” or “Incident” tables under the Jira Service Management section. These custom fields still appear listed in the “Issue field” table though.

To query your custom field data in SQL mode, you can use a simple query like the below code as a starting point. Depending on the chart you want to make you can then join the “Issue field” table with other tables to include additional columns or filters.

SELECT name, value 
FROM jira_issue_field 
WHERE name = 'Work category'
GROUP BY ALL

If your custom field isn’t a string data type, then you might need to change the data type of the custom field within your SQL query. The example SQL query below is using the CAST() function to change the data type of the Value column to be real since the Story Points custom field can have decimals. Then you’ll be able to further customize your chart querying custom field data.

SELECT name, CAST(value AS real)
FROM jira_issue_field 
WHERE name = 'Story Points'
GROUP BY ALL





Last modified on Nov 18, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.