Joining Various Tables - Jira

Hello,

I am new to the Jira Schema, but I'm trying to join a few tables and can't figure out which ID relates to others in the tables.. I attempted to review this:

https://developer.atlassian.com/jiradev/files/4227160/45525621/1/1486695169318/jira70_schema.pdf

which made my head hurt a bit! Can anybody help me figure out how to join these tables....

.Project

.JiraIssue

.IssueType

.ChangeGroup

.ChangeItem

.CustomField

.CustomFieldValue

1 answer

0 vote

Reading the JIRA database is usually a terrible way to do anything, as it's "designed" to be a datastore for JIRA, not any form or reporting tool.

Could you explain what you are trying to achieve?  As there is a better way to do it than using the database.

-----

But, to answer the technical part, start with the JiraIssue table.  That has

A project field, which you can use to read the project table

An issuetype field which contains an id that is the id in the issuetype table

an issue id on it (a unique immutiable one, not the ones the humans see)

The issue id can be used to find a ChangeGroup record, which is a container for the lines of history with a date and user on them.  You use the id in the changegroup to identify the changeitem lines that belong to a group, and those tell you each field that changed during that update

CustomField is a header table, describing what the custom fields are

CustomFieldValue contains the actual values for issues - you'll find the issue id and the CustomField.ID on them to tell you which issue data is hed in which field.

As you can see from that, it's really not a nice layout for reporting.  Hence the advice that you don't want to do it this way.

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 ...

3,315 views 14 20
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