SQL for JIRA


Examples


Example 1: Parent-Subtask relationship

What is the SQL for JIRA showing the issues having sub-tasks in a project? 
    select distinct 
                   i2.key
    from
                issues i1
                        inner join
                issues i2
    where
                i2.id=i1.parentid 
                    and
                i1.jql='project=MYPROJ and type=Sub-task' 

   

         
Example 2 :  Work logs
A) What  is the SQL for JIRA showing the issues that a user has worked on today? 

             select 
                    distinct i.key 
             from 
                    issueworklogs w 
                        inner join 
                    issues i on w.issueid = i.id 
              where 
                    i.jql='updated >= startOfDay() AND participants = <username>' 
                        AND 
                    w.author = '<username>'
B) How much business time has a issue spent among the created and the resolved status? 
             select 
                    FORMATDURATION(DATEDIFF('SECOND', i.created, i.resolved), true) as "Duration"
             from
                    issues i
              where 
                   i.key='ISSUE-1234'



Example 3: Issue activity (change history) on an issue field:

You might want to know how a custom field value (or whatever other issue filed, of course) has changed along the time. This is feasible via the ISSUECHANGES table.

Given an Agile project, how have their Sprint contents changed along the time?

NOTE: Sprint is a custom field created by Agile
 

        select
                    i.key as "Issue",
                    c.username as "User",
                    c.created as "Date",
                    c.fromvalue as "Older value",
                    c.tovalue as "Newer value" 
        from
                    issuechanges c
                        inner join
                    issues i on i.id= c.issueid
        where
                    c.field='Sprint'
                        and
                    jql='project=SSP'
        order by 
                    c.created asc

You might want to execute the SQL for JIRA query above from the provided built-in database web console:



Example 4: Using grouping functions (SUM, COUNT,...) on custom fields:

NOTE: This example is based on JIRA 6 and Agile. However, it can be extended to any issue field in a straightforward way!

Agile creates some custom fields like the Story Points to estimate the effort required by a issue. Imagine that you want to answer to this question:

What are the Agile projects which are currently requiring more effort? 

1. In order to get issues contributing to the "current" effort you have to run the JQL query : Sprint in openSprints().
    
        select 
                        * 
        from 
                        issues 
        where 
                        jql='Sprint in openSprints()'

2. You have to group the issues above by project and summarize the Story Points which are internally stored in a custom field. This is achieved with the SQL for JIRA query below. For demonstration purposes only, a 10 story points threshold has been established:

select 
    p.id 
from 
            issues i     
                    inner join 
            issuecustomfields cf on cf.issueid = i.id    
                    inner join 
            projects p on p.id=i.projectid 
where 
            jql='sprint in openSprints()' 
group by
            p.key 
having 
            sum(cf.story_points) > 10

3) Now, you know the project ids  which require a total effort > 10 story points. You have to get the issues contributing to such efforts. You can get it by nesting the two queries above to get the issue keys:

          select 
                        i.key
          from 
                           issues i 
         where 
                          jql='sprint in openSprints()' and i.projectid in (
                                    select 
                                                    p.id
                                     from 
                                                    issues 
                                                            i inner join 
                                                    issuecustomfields cf on cf.issueid = i.id 
                                                            inner join 
                                                    projects p on p.id=i.projectid 
                                    where 
                                                     jql='sprint in openSprints()'
                                    group by 
                                                    p.key
                                    having 
                                                    sum(cf.story_points) > 10
                                                                                                    )

4) Finally, you have to use the built-in sql JQL function to run the query (3) from the JIRA Issue Navigator as shown on the picture below:


5) Save it as filter to be reused later!



Example 5: Embedding SQL queries in JQL filters and JQL filters in SQL queries:
 

Epics with no issues?
        select 
                    i.key 
        from 
                    issues i 
                            inner join 
                    issuecustomfields cf on cf.issueid=i.id 
        where
                    jql='type=Epic' 
                            and 
                    cf.epic_name not in
                                select 
                                    distinct cf.epic_link 
                                from 
                                    issues i 
                                            inner join 
                                    issuecustomfields cf on cf.issueid = i.id 
                               where
                                            jql=' "Epic Link" in sql("select key from issues where jql=''type=Epic'' ") ' 
                                                    )
    

Please, pay attention to the SQL where clause below:
         where 
                         jql=' "Epic Link" in sql("select key from issues where jql=''type=Epic'' ") ' 

...the JQL condition internally executes a SQL query!



Example 6: Visual report by using the SQL for JIRA Reports & Gadgets free extension

The Reports & Gadgets extension provides an example report (a xml file with the BIRT .rptdesign file extension) to show the JIRA user's issues. 

1)  Download the example xml report from the plugininstall it on your local Eclipse and modify it according to your needs. Edit the JDBC Datasource to point it to your JIRA instance and use your JIRA user's credentials to connect to your JIRA instance and see your data:


2) Look at the existing DataSets for some basic SQL for JIRA integration examples for tables and charts:


3) Once your report is ready, you can upload and embed it in a JIRA Dashboard gadget: