SQL queries in Jira Cloud

Despire being a powerful project management tool, Jira Cloud doesn’t support complex data analysis out of the box. Including one of the most powerful instruments for data analysis – SQL.

That’s why we created SQL for Jira Cloud – an app allowing to run SQL queries agains Jira Cloud Data without need to configure data connections or data lakes. All out of the box.

Let’s dive into a couple examples of what you can do with SQL for Jira Cloud.

Issues

One of the key features of SQL for Jira Cloud is the ability to work with data across multiple projects. This is particularly useful when you need to create reports or track changes across all projects.

For example let’s say we want to see all issues that are in status ‘In Progress’ for more than 10 days. We can use the following query:

SELECT * FROM issues WHERE status = 'In Progress' AND DATEDIFF(NOW(), updated) > 10;

Now let’s say we want to see just a number per project to see where the most issues are stuck in ‘In Progress’ status. We can JOIN projects table and add some grouping:

SELECT projects.name, COUNT(*) FROM issues
INNER JOIN projects on projects.id=issues.project_id
WHERE issues.status = 'In Progress' AND DATEDIFF(NOW(), updated) > 10
GROUP BY projects.name;

Changelogs

Each change to Jira issue, be it status change or a change in the field value, is recorded in the changelog. This makes it a very powerful source of information for data analysis yet not available from JQL.

With SQL for Jira Cloud you can easily query the changelog table to get the history of changes for any issue. This can be used to create reports, track changes, or even to create custom fields based on the history of changes.

For example if we want to see all status changes for a specific issue, we can use the following query:

select issues.key,from_value, to_value from changelog
INNER JOIN issues ON issues.id=changelog.issue_id
WHERE field_id='status'

AI assisted queries

Let’s say we want to understand how many issues where reopened during last 3 months. We will consider issue reopened if its status has been changed to ‘In Progress’ at least twice.

It’s somewhat complex SQL query to craft. Exactly for those cases we have AI assisted queries allowing you to explain data request in the “human” language and let AI convert it into SQL for you.

For example let ask AI what we want to get:

return me list of issues that have been in status 'In Progress' at least twice during last 3 months. Show number of times each issue has been in In progress status as well.

As you see from the screenshot, AI created a SQL query with data aggregation and executed it for us returning issue IDs and number of times they have been in ‘In Progress’ status.

Now let’s ask it to return issue key instead of ID:

return me list of issues that have been in status 'In Progress' at least twice during last 3 months. For each issue show issue key and number of times it has been in In progress status.

This time AI figured it needs to JOIN issues table to get issue key and returned the result we wanted.

The same way as queries statues changes we can work with any other field in Jira, including custom fields, which in combination with powerful filtering and aggregation possibilities of SQL can provide you with deep insights into Jira data.

SQL queries for cross-project overview with help of AI

Another example of how SQL for Jira Cloud can be used to create an overview across all projects. Let’s say we want how large is backlog of each project in one overview. Let’s ask AI to create a query for us:

show me for each project how many issues are in "To Do". Output project name and number of issues.

Now let’s say we want to see more elaborate status, specifically: backlog, in progress and done issues. Let’s ask AI again:

show me for each project how many issues are in "To Do", "In progress" and "Done " statuses. Output project name and number of issues is each status.

What we didn’t tell AI is that in some projects “Done” status is called “Launched”, so let’s go and update the query it generated to include “Launched” status as well:

SELECT projects.name AS project_name,
       SUM(CASE WHEN issues.status = 'To Do' THEN 1 ELSE 0 END) AS todo_count,
       SUM(CASE WHEN issues.status = 'In progress' THEN 1 ELSE 0 END) AS in_progress_count,
       SUM(CASE WHEN issues.status = 'Done' OR issues.status='Launched' THEN 1 ELSE 0 END) AS done_count
FROM projects
LEFT JOIN issues ON projects.id = issues.project_id
GROUP BY projects.name;

As you can see it now picked up “Launched” status in project “Go to market sample” and counted it as “Done”.

To summarise

There is much more you can do with SQL for Jira Cloud. It’s a powerful tool that can be used to create custom reports, track changes, dive deeper or create an overview. Especially in the hands of skilled professionals who understand both Jira and SQL, it can be a game changer.

Feel free to reach out if you’d like to get more information via our Contact Form, would be glad to get in touch!

We have a free trial available, please check it out on our Atlassian Marketplace listing: