JIRA database customfieldvalue query

[Davide Monzani]

Hello, i'm trying to extract from the jira db a customfield information for each issue, everything is good when the information is 'stored' in the stringvalue (customfieldvalue table)

SELECT
CONCAT(p.pkey, '-', ji.issuenum) AS Issue, cfo.customvalue as Customer
FROM
jiraissue ji,
project p,
jiradb.customfield cf, jiradb.customfieldvalue cfv , jiradb.customfieldoption cfo
WHERE
p.pname = 'pname' AND cfo.CUSTOMFIELD='field'
AND ji.project = p.id
AND ji.id = cfv.issue
AND cfv.customfield = cf.id
AND cast(cfv.stringvalue as integer) = cfo.id;

i use a query like that and i get what i need.

for another custom field i noticed that the stringvaule and textvalue is null, and the info is in the numbervalue (customfieldvalue table) 

if i use the query above it returns 0 rows, ive tried also another couple of modification and always get no results.  Anyone can help me please? 

[Nic Brough -Adaptavist- · Accepted solution]

Yeah, this is one of many reasons you should not be reading the database.  It's absolutely the worst way to get data out of Jira.

You're going to need to construct a lot more SQL - you need to read the custonfield table to establish what type of field it is, and then conditionally read one of the olther columns for the data value.  When the value is an option (which it will be for select type fields  - check boxes, select, multi-select, radio button etc), you'll need yet another join on to customfieldoption to look up the display value

Please, stop reading the database, it's a dreadful way to get data from Jira