nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Accepted answers

Akash Panchal

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.

All comments

Matt Doar_tzgiej

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

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

I agree, my options were either using:

- SQL DB query

- REST API

- ScriptRunner groovy script, for example

Thanks for sharing!

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!

Akash Panchal

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

database

data-center