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

[Hamdy Atakora]

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

[Andy Heinzer · Accepted solution]

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

[Hamdy Atakora]

@Andy Heinzer  Thank you very much. This worked just the way i wanted it. I tried the JQL and i couldn't get it to display the labels distinctly. That is why i figured the SQL route would be my best chance here.

I really need to learn more about SQL for jira. Thank you for all your help.

[Alex Gallien · Accepted solution]

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.

[Hamdy Atakora]

I did . JQL doesn't allow me to export it the way i'd like.

As you know, a multiple labels can be used in one issue. But i want my list to be a list of distinct labels. When i try with JQL and try exporting it gives me a list of distinct issues with a label column containing multiple labels