Need the Oracle query for the Export Spreadsheet functionality

Question

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.

Nic Brough -Adaptavist- · Answer

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

Sanjay16 · Answer

Thanks Nic ! Appreciate your immediate response

Nic Brough -Adaptavist- · Answer

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