How to get the customfield "default value" value using SQL query

[aarti rajput]

I am successfully capture the data from two tables as required but still am not able to get the default value of custom field which is initially set at the time of field creation.

Query:

select cf.ID "Custom field ID", cf.cfname "Custom field Name", cf.defaultvalue "Default Value",
TRIM('com.atlassian.jira.plugin.system.customfieldtypes:' from cf.CUSTOMFIELDTYPEKEY) "Type", cfd.customvalue "List of values"
from customfield cf JOIN customfieldoption cfd ON cf.ID = cfd.CUSTOMFIELD
where cf.CUSTOMFIELDTYPEKEY LIKE "%multiselect%" OR
cf.CUSTOMFIELDTYPEKEY LIKE "%select%" OR
cf.CUSTOMFIELDTYPEKEY LIKE "%radiobuttons%" OR
cf.CUSTOMFIELDTYPEKEY LIKE "%checkboxes%";

Its giving me the below output: Where Default value for all custom field is NULL. it should give the exact default value which is set at the time of field creation.

Custom field ID	Custom field Name	Default Value	Type	List of Values
10112	Initiative	NULL	select	Yes
10117	T-Shirt Size	NULL	radiobutton	XL
10117	Size	NULL	multiselect	L

[Filipi Lima]

I'll post this here even two it's been two years because I did run across a case where I wanted to double check database for a complex case on Data Center. I had to run this ("DATAKEY" is the ID from configurationcontext, which refers to the context):

select * from genericconfiguration WHERE DATATYPE='DefaultValue' AND DATAKEY='12700'

Result is: 

11201,DefaultValue,12700,<long>10501</long>

Where 10501 is the option id, from customfieldoption table.

Each context will have a value like above. A bit more details on:
https://developer.atlassian.com/server/jira/platform/database-custom-fields/

[Manisha Kharga _Appfire_]

Hi @aarti rajput 

Thanks for submitting your question!

We have created SUPPORT-4323 to track the issue. Please access the Support request SUPPORT-4323 and once you signup, please let us know your username so that we can add yourself as the reporter to the support request.

We would like to inform you that using the SQL query itself is not showing the custom field value in the defaultValue column, the value of the custom field is resulting under the List of Values column. So it is not possible to get the default value of the custom field using the Jira CLI app.

Please let us know if you have any queries.

Thanks,
Manisha

[aarti rajput]

That means i cant have the default value for custom field using SQL query.

Any other alternative where i can get the default values for all the custom fields.

[Manisha Kharga _Appfire_]

Hi @aarti rajput 

Thank you for your response.

We will not be able to retrieve the default value for the custom field using SQL query because the default values are not getting stored in the database.

It is not possible to get the default value of the custom field using the Jira CLI app. You can refer to the Jira CLI app example page to view the actions which will automate your daily tasks.

Thanks,
Manisha

[Dave Theodore [Coyote Creek Consulting]]

Why wouldn't you do this in the REST API? It's designed to do this. You can also use the Jira CLI, which makes things even easier.

I would highly recommend not connecting directly to the database for this sort of thing. First, as you have already discovered, the data model is challenging. Second, any script you write has the potential to break when you upgrade, as the schema often changes with a new Jira version. Unlike the database schema, the REST API doesn't change from version to version.