querying custom field values

Bryan Collick

Let's say i have:

• a self-managed JIRA instance on a mysql db
• a JIRA project [project A] with an id of 10101
• a mandatory custom field (importance) for all project A issues (cf id 12321)
• three options for this custom field: high, medium, low

Let's also say I'm not super confident in my sql skills (my jql is way better). Since project A is the only project using this importance field, I'm doing something like:

SELECT `Customfieldoption`.`CUSTOMFIELD` AS `CUSTOMFIELD`,
       `Customfieldoption`.`customvalue` AS `Customvalue`
FROM `customfieldoption` AS `Customfieldoption`
WHERE ((`Customfieldoption`.`CUSTOMFIELD` = 12321))

and what I end up with is three rows for each issue--it's showing me ALL OPTIONS rather than the selected option. Is this:

1. an error in my query because I'm sql weak, or
2. an error in my query because there's table data I'm missing for the SELECTED option?

Jeff Turner_gyz2nh

SQL is absolutely the best way to get JIRA data You just need to know a few table facts:

• As you've discovered, 'customfieldoption' stores the possible values for a select-list. Join customfield.id to customfieldoption.customfield.
• Per-issue custom field values are stored in 'customfieldvalue'. If the custom field is a select list, the 'stringvalue' field will contain the ID of the relevant customfieldoption row. So join jiraissue.id to customfieldvalue.issue and customfieldvalue.stringvalue (cast as integer) to customfieldoption.id.
• project.id joins to jiraissue.project. Hint: get the issue key with CONCAT(project.pkey, '-', jiraissue.issuenum).

To learn the JIRA tables behind a particular operation, a useful trick is to generate a text dump of the database before the operation (e.g. editing a customfield) and after, then diff the two text dumps. See https://www.redradishtech.com/display/KB/Using+database+diffs+to+see+what+JIRA+is+doing