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.

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.

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.

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.

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’)

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