querying custom field values

Bryan Collick July 6, 2017

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?

3 answers

1 accepted

1 vote
Answer accepted
Jeff Turner July 6, 2017

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

Bryan Collick July 7, 2017

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!

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 7, 2017

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.

Like # people like this
Bryan Collick July 7, 2017

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 7, 2017

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.

Like Zaqueu Santana likes this
0 votes
Rodrigo Nunes February 13, 2020

select o."NAME", concat(pro.pkey,'-',ji.issuenum) from "AO_54307E_ORGANIZATION" o
join "AO_54307E_ORGANIZATION_MEMBER" om on om."ORGANIZATION_ID" = o."ID"
join jiraissue ji on ji.reporter = om."USER_KEY"
join project pro on pro.id = ji.project
where o."NAME" = 'Organization name';

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 6, 2017

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?

Bryan Collick July 7, 2017

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