SQL: customfieldvalue of issue number with multiple projects

[John Smith]

Hey guys,

I'm working on the JIRA DB with SQL. And there is no other way in my project.

So, I want to get the custom field values of various issues. Easy, in the table CUSTOMFIELDVALUE you have the field ISSUE which is a link to the table JIRAISSUE.

But what if a custom field is used in different projects? Then you have a 1:n cardinality.

How do I get the customfield value for ISSUE A in Project X and ISSUE A in PROJECT Z? 

I am not able to solve this problem. I would expect to find atable where CUSTOMFIELD (bad naming btw since this probably used as a id) ISSUE (inconsistent naming, since this is the field ISSUENUM in JIRAISSUE) and PROJECT are associated. The only table I can find this is CONFIGURATIONCONTEXT. But this only works when there is a data in CUSTOMFIELDOPTION. This is not always the case. 

One could go deeper an find out the field layout whether the field is hidden or not, depending on the project. However that is way to complicated. Can anyone tell me how this can be solved easily.

Thanks in advance! Beni

[John Smith]

First of all.... I have no option to switch from SQL since I am a BI ETL guy. 

With my architect I found the solution:

The table JIRAISSUE has the field ISSUENUM and ID. ID is unique, ISSUENUM  not since it can be equal for two projects.

In CUSTOMFIELDVALUE you have the field ISSUE. The join has to be done between JIRAISSUE .ID and CUSTOMFIELDVALUE.ISSUE the you have the correct association between the customfield and the ticket. 

This is not intuitiv due to bad naming. In the DB scheme you can see that one table has field with the -ID Appendix and sometimes not. Naming every first field in every table ID is also not clever. Dear JIRA developers, you should get an DB expert and fix the naming quality since these are definitly technical debts. But at the end it's your decision...

[Nic Brough -Adaptavist-]

Bear in mind this could change at any time, and it's only true for more recent versions of Jira.

It's nothing to do with design or "bad naming".  It's due to moderately good design being degraded by evolution over the years.

There's no need for the Jira developers to fix anything in the database, there's no technical debt there.  Because it's a database that is never intended to be directly read.  It's not a database, it's a data storage facility.  The real "fix" is to ignore the data-store and read the data the right way.  Via the application that actually has all the relationships and presentation.

[Nic Brough -Adaptavist-]

I'm afraid you are "hoist by your own petard" here.

>One could go deeper an find out the field layout whether the field is hidden or not, depending on the project. However that is way to complicated.

It is way too complicated.  It is one of the reasons that you should not be trying to do this via SQL - it's hard. 

To answer your question, there is no easy solution in SQL, you are going to have to understand and write queries for every convoluted case in there.