Retriving all issue in project via database query

[Jordan Berry]

I am trying to get all of the issues in 2 projects through the database (Postgresql). I would like to get about 20 fields, some system and some custom. I am relatively new to writing these queries, so I am not exactly sure what to look for. 

The reason I am trying to do this, is between 2 projects we have roughly 10,000 issues, and I need to do a mass export to get into a separate list to query against. This would allow users in these projects to have an easier time searching through their data, as they are finding the built in search/jql too confusing (and complex due to our custom fields)

Does anyone have an example query I can reference that shows how to select a project and multiple fields?

[Nic Brough -Adaptavist-]

This is the worst possible way to get data out of Jira. 

Jiraissue is a table containing issues.  You need to join it to the project table, then a pile of tables that relate to understanding the system fields attached to an issue, then at least 2 (and potentially hundreds) more joins to understand each custom field. 

Where your users are finding JQL "too confusing", they're going to find SQL thousands of times more confusing and horrendously slower.

Please, get them to work with JQL.  And if your custom fields still confuse them after you've got them working with the basics, then you really need to do something about your custom fields being over-bearing.

[Jordan Berry]

I see. So in that case, I might do my backup plan which is to manually export the issues as CSV though OOTB jira search/export.

To clarify what I am trying to do, we have a need for our users to be able to search for specific records that we store in jira, relating only to their company function. JQL and basic jira search is not cutting is, especially since we are using nFeed fields. My intention would be to get a 1 time snapshot of all the issues in their projects, import them into a Sharepoint list, have MS Flow get all new/resolved issues put into that list, and then use a tool like PowerBi to query off of that list.

I know there are jira connectors for powerbi, but they do not grab every custom field, and it pulls the entire instance, instead of just our few projects needed. I have done this before with other projects and found success, but none of those projects were over 2000 issues yet.

[Jordan Berry]

The end user would not be using JQL/SQL at all, they would be looking at another search tool/PowerBi to get quick info on their issues

[Nic Brough -Adaptavist-]

Any external tool is going to give your users an inferior search to that built into a tool, or one built to understand the main tool in full, as they won't be as flexible or powerful as something that thoroughly understands the data.

Please, forget this approach, it's not good.  Get your users to learn JQL properly (it's certainly a metric shed-load easier than Sharepoint, MS Floe and PowerBI hooked up to a system that none of those understand).  Then consider proper reporting Apps that do understand Jira - EazyBI, Dataplane etc.