querying custom field values

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?

2 answers

1 accepted

Accepted Answer
0 votes

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

I think this is what I'm looking for. Let me try it first and then I'll approve this answer. Thanks in advance, Jeff!

Jeff, I think the complexity of your answer to get a really simple basic thing proves the point - SQL is by far the worst way to do reporting on JIRA.

Nic, what would be a simpler way to get this information?

Depends on the context of what you're doing, but there's two basic ones.

If you're working inside JIRA, in an add-on, then the API has several simple "give me custom field value from issue X" options.

From outside JIRA, a single REST call fetches the whole issue in one step (and if you make it a bit longer, you can ask for a single field)

SQL is an utter disaster for reporting in JIRA.  Don't do it.

0 votes

SQL is absolutely the worst way to get to JIRA data, and should not be used in this way.  As a simple example, you now need to join over 40 tables (many of them more than once) just to get the data for a single issue.  Do not use SQL to do it.

(And, please, when posting questions, drop the use of "as", it does nothing more than save you a bit of typing while making your SQL a lot harder to read)

So, moving on, you don't say what question you are trying to answer with this query.  What are you looking for here?

Apologies for the illegibility of my sql. As noted, sql is not one of my strong points. 

At the micro level, I'm looking for a list of project A issues with their corresponding "importance" value. At a higher level, I'm hoping to use the query in Chartio to present a stacked bar chart of issues created monthly, the bar stacks representing "importance."

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 18, 2018 in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

21,996 views 2 7
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you