How to join customfields to project table

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

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?

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. 

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

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
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,304 views 12 19
Read article

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