How to join customfields to project table

SABARISH RAJ August 8, 2017

There are custom fields created in the front end, which is stored in the public.custom field table in the backend. The values are stored in the public.customfieldvalues table. I want to join this data to projects table. I am not able to find a link to join this data. If anyone has any idea about this, if you could please help me it would be great

1 answer

1 vote
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.
August 8, 2017

This is a query that involves a lot of joins and requires you to really thoroughly understand the JIRA database.  Far too many people don't, because it's not the right way to do it.

SQL is the worst possible way you can interact with JIRA data, because it has been built up as a data store, not a reporting system.

The best answer to this question is "don't look at the database, please tell us what the question is."  Please tell us what you are trying to do in front-end terms.  What question are you trying to answer with this query?

SABARISH RAJ August 8, 2017

Thanks for your reply. I am building a report based out of JIRA data, so basically extracting Jira data through SSIS and storing it in a centralised repository. This centralized repository has data from systems like (svn&git(source control). The bottom line is I have to build a report that involves data from jira, svn and git. So i need to capture the above-mentioned data from jira (which is custom field and its values and join them with svn)in order to join them with svn, i need the project number associated with the custom field and its values, hence I posted the question. I hope this is clear, if not let me know , I can explain more. 

SABARISH RAJ August 8, 2017

if you could please tell me the joins, that will help me 

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.
August 9, 2017

That's a non-starter because fields do not belong to projects, they belong to issue types within projects.  A bug in project XYZ can have a field that bugs in ABC do not.

To find out if a field is present in a project, you are going to need to read the tables that hold the custom field contexts, the field configurations and screen definitions, and parse the xml for the workflows. 

Once you have the field, you'll need to read customfieldvalue for any issues you want to report on, and then conditionally join that to a range of other tables depending on the content you find based on a join to customfield (to read the type)

In short, you've landed on the worst possible way to do reporting from JIRA.

What you should do is use the REST API to read the issues you need.  No joins or understanding of the database is needed, and you won't kill your JIRA database with horrible queries.

Suggest an answer

Log in or Sign up to answer