Need the Oracle query for the Export Spreadsheet functionality

Sanjay July 27, 2015

Hi , I need the oracle query which is used for exporting all my Issue types and the corresponding column values. Basically I dont want do a export from the Jira front end , instead I want to get the same data from the Back end through a Query. Can you please help with this . I need this since this data will be consumed by another internal reporting tool of our system . Thanks for understanding . Please let me know if you have any other queries.

2 answers

0 votes
Sanjay July 28, 2015

Thanks Nic ! Appreciate your immediate response smile

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.
July 28, 2015

Sorry that the answer is "your approach is wrong", rather than "here's a nice solution"

0 votes
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.
July 27, 2015

That is something you will have to build and maintain for yourself.

First, you will need to write a search query that matches the JQL you want to run.

Tthen for each issue, you will need to read jiraissue for the main fields, nodeassociation and versions and components for the versions and components (many to many relationship there), customfield for the customfield headers, so that you can then read customfieldvalue for the right columns and then, in later versions of Jira, options for the select list type fields, and I'm pretty sure I've missed a few, even before we start talking about what Agile, Portfolio, Tempo or other addons may be doing.

What I'm saying here is that this is not a simple query, you need to build and maintain a data dictionary for your JIRA, one that is fully aware of your version of JIRA and any addons you might be using, and we can't hand you that on a plate. 

A better solution would be to get your reporting tool to ask JIRA properly for the data.  Use REST to run the JQL and parse the results (that would be database and version independent), or run the JQL and scrape the resultant xml.  Or you could use one of the reporting plugins which understands JIRA data already.

If you insist on doing it the hard way and making weeks of work for yourself, start at https://developer.atlassian.com/jiradev/jira-architecture/database-schema

Suggest an answer

Log in or Sign up to answer