querying values for customfield in Jira database

Stephen Zadroga

I trying to retrieve values for a customfield in the backend Jira db.  I was able to query the customfield in the customfieldvalue table, but the values are returned as integers within the STRINGVALUE field.  My thought is this another lookup / ID value, but I can't seem to find the table that holds the string values for this customfield.  Any help is greatly appreciated!

Nic Brough -Adaptavist-

Yep, this is one of many reasons I recommend doing reporting properly (with apps built for it, or asking Jira for data over the REST API) - the database is the worst possible way to report on Jira data.

But, if you insist on doing it the hard way

The table you are looking for depends on the type of field it is.  My guess is that it is a list field of some sort (select list, multi-select list, radio button, checkboxes, those sort of labelled items).  If it is a list field, then these contain "options" which you'll find in a table called Options (from memory - the name definitely has the word "option" in it)

The numeric string id you've found in customfieldvalue is the key to the options table - customfieldvalue.stringvalue = options.id 

You'll be able to get the human display name out of the options row.

(Note - some other fields do this too, like user-pickers, but you'd read a different table for them.  And cascading-select lists are a right botheration to read because you have to check the options table twice)