Where can I find the values for a custom field in JIRA DB

[Vishnu C]

I created a custom field with JIRA dark feature(Multi Select with auto suggest option). I'm trying to find the list of values added to this custom field from JIRA DB. I have the below query but the results are showing the text value as null and the string value as 11100 and 11101. 

select * from JIRA.customfieldvalue where customfield = 11600;

ID         ISSUE CUSTOMFIELD        PARENTKEY           STRINGVALUE 

189534 27704 11600                    1575867073700     11100
189535 27704 11600                    1575867073700     11101
189536 27705 11600                     1575867252062    11101

[tom lister]

Hi @Vishnu C 

You'll need to link the string value to the id in customfieldoption to get the user friendly version.

e.g.

SELECT cf.id, issue , cfkey, customfieldtypekey, cfname, cfv.stringvalue, cfo.customvalue
FROM public.customfield cf, public.customfieldvalue cfv , public.customfieldoption cfo
where cf.id = cfv.customfield
and cast(cfv.stringvalue as integer) = cfo.id
and customfieldtypekey like '%select%'

[Vishnu C]

@tom lister 

Thank you what is the purpose of Issue ID in customfieldvalue table. My custom field id is 11600 when I query this id in CFV table it has 3 entries for string value and 1 of them is repeated with same Issue ID and Parentkey. Can you please let me know what is purpose of Issue and ParentKey in this table. Reason why Im asking is we are planning to load values for this Cutomfield from outside service so want to make sure what fields and tables I need to take care when I make an entry

[tom lister]

Hi @Vishnu C 

The customfieldvalue table is the actual option(s) selected and associated with an issue, so Issue ID is the link to jiraissue. Not sure about parent key wothput a bit of research.

The available values for a select  list are the customvalueoption entries.

As for updating values, I personally avoid SQL updates as experience has shown there can be unexpected problems. There are a lot of potential collateral updates by the API's and I no longer feel like the risk is worth the possible time saving. 

I recommend using Groovy scripting. e.g.https://community.atlassian.com/t5/Marketplace-Apps-Integrations/add-options-to-select-list-using-groovy/qaq-p/100966

There are some free Groovy plugins out there.

Tom