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 votes

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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,811 views 11 18
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot