Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

querying values for customfield in Jira database

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!

1 answer

1 accepted

1 vote
Answer accepted

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)

Thanks for the prompt response.  Yes, this field is some type of list field.  Let me explore the tables where the name contains "options" in it.

I checked both the customfieldoption and optionconfiguration tables, but no luck.  The customfieldoption table holds a field called customvalue but the values are not what I am looking for.  I am looking for "Sprint" values...is it possible they are in a different table?

I have the ID and cfname for the customfield and I can find these in the customfieldvalue table, but I cannot find a way to look up the STRINGVALUE for this specific CUSTOMFIELD.

I tried querying the customfieldoption table for the ID of this customfield, but no records are returned.  Makes me believe these "sprint" values are in a whole different table.

Oh, sprints, yeah, they're not options, they're sprint objects.  I've never looked for them, but I do believe most of the sprint data is in the active objects tables that Jira Software creates.  Tables are prefixed with AO_60DB71 (crib sheet for which app creates which tables is at https://confluence.atlassian.com/jirakb/list-of-jira-server-ao-table-names-and-vendors-973498988.html - I'm not going to pretend I could remember the table keys!)

that's it, THANK YOU!  I completely overlooked the AO_ tables.  I was able to JOIN the customfieldvalue table and AO_60DB71_SPRINT on STRINGVALUE = ID to the display value.

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you