How to retrieve all custom field configurations and custom field values in Jira Data Center from the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article aims to assist in retrieving all custom field configurations and field values from the database in a Jira instance.
Environment
All currently supported Jira Server and Data Center versions
Diagnosis
This is not a native feature available in Jira and will need to be performed by querying the database directly.
Solution
Please note the following information is provided as-is since using SQL for business intelligence is beyond the scope of Atlassian Support.
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Retrieve the definitions of all custom fields configured in the Jira instance.
1 2select cf.id, cf.cfname, cf.description, cfo.customvalue, cfo.disabled from customfield as cf join customfieldoption as cfo on cf.id = cfo.customfieldRetrieve all custom field value data for all issues in the Jira instance.
1 2 3 4 5select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.projectTo retrieve all issues to a specific custom field name (exclude select list field that has option values):
1 2 3 4 5 6select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.project where cf.cfname = 'CUSTOMFIELDNAME';To retrieve all issues from a select list field type of the existing options values:
1 2 3 4 5 6 7select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfo.customvalue, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join customfieldoption as cfo on cfo.id = CAST (cfv.stringvalue AS INTEGER) join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.project where cf.cfname = 'CUSTOMFIELDNAME';
Was this helpful?