List of all issue types per projects in Jira - SQL query

[Akash Panchal]

Hey everyone,

I'm looking for a way to write a SQL query that gets a list of issue types per project. For example:

project XYZ --> Uses issue types A, B, C

project ABC --> Uses issue types D, E, F

.

.

.

.

etc. 

So far, I'm thinking that I will need to use 3 tables to get this sort of data:

project

issuetype

jiraissue

The query below allows us to get a list of projects that use a certain issue type by passing the issue type ID, but I'm looking for an easier way to simply pull all issue types that a project uses by maybe passing the project ID?

SELECT DISTINCT P.pname, P.pkey, P.lead AS project
FROM jiraschema.project as P
INNER JOIN jiraschema.jiraissue as J on J.PROJECT = P.ID
INNER JOIN jiraschema.issuetype as I on I.ID = J.issuetype
WHERE J.issuetype= 10106

Thank you!

[Akash Panchal · Accepted solution]

Sharing a Groovy script that worked for me (using ScriptRunner console):

Output of data is a bit clunky since it shows within the console output, but it works! Pulls issue types for all projects by iterating over the Jira instance projects. 

Hope this helps as well. 

[Kai Becker]

Hi @Akash Panchal 

building up on your query, the following modification might do the trick:

SELECT
P.pname,
STRING_AGG (DISTINCT I.pname, ',')
FROM project as P
INNER JOIN jiraissue J on J.PROJECT = P.ID
INNER JOIN issuetype I on I.ID = J.issuetype
GROUP BY P.pname;

Please be sure to double check the results with a simple project.

[Akash Panchal]

Thanks for the response, would I need to place the project name within the single quotes? 

[Kai Becker]

Hi @Akash Panchal 

what do you mean? :)

This query returns all issues with alle issue types. 
First column contains project name and second column the list of issue types.

Forgot to mention: this is testet on postgresql database

If you want to get a list for a single project, you could add a WHERE clause with the project like this

WHERE p.pname = "Abcde"

[Akash Panchal]

@Kai Becker I apologize, random brain fog! This helps, thank you!

[Taranjeet Singh]

@Kai Becker maybe, you mean using:

HAVING p.pname = "Abcde"

since the SQL query is using GROUP BY clause.

[Matt Doar]

A Jira project has an issue type scheme listing the issue types that can be used.

Not all may actually be used

An issue type scheme has a set of issue types

Issue types have their own table in the DB.

This info is usually easier to extract using a script in the ScriptRunner console, or perhaps via the REST API

[Akash Panchal]

I agree, my options were either using:

- SQL DB query 

- REST API

- ScriptRunner groovy script, for example

Thanks for sharing!