Jira SQL Query to Return all Labels from the Label field from a Specific Project

Question

Hello and thanks in advance for the help.

Is there a way to write a  SQL query to return a list of all labels used in a project A and the issues associated with it?

Essentially i want to run a SQL query but instead of the version, i want to see the labels used in the label field for a specific project

Project, Project Key, Issue ID, Issue Key, Label

I really need to learn more about querying the Jira DB with SQL

Alex Gallien_ccu84u · Accepted Answer

Why do you need this as a SQL query? Have you considered trying to export the issues to a CSV instead - Exporting an Issue? There  is almost always a better way to report on your data then directly pulling it from the database.

Andy Heinzer · Accepted Answer

Hi Hamdy,

While you probably could find this info via JQL the way Alex suggests, I did some investigating and I think i found a way to provide what you are looking for here via SQL.

select p.pkey || '-' || ji.IssueNum as IssueKey, l.label, p.pkey, p.pname
from label l
join jiraissue ji on ji.id = l.issue
join project p on ji.project = p.id
order by issuekey asc

This query will return all the labels, and then give you the issue key, project key, and project name.  You will find that issues that have more than one label will be displayed in this list multiple times.   But this is one way to try to organize and understand which labels are used on a per project basis.   You could even restrict this query to only return issues in a specific project with something like:

select p.pkey || '-' || ji.IssueNum as IssueKey, l.label, p.pkey, p.pname
from label l
join jiraissue ji on ji.id = l.issue
join project p on ji.project = p.id
where p.pkey='SSP'
order by issuekey asc

This would only return issues in the project with the key SSP.

Cheers,

Andy