Using JQL with SQL (from to) with SQL driver for Jira

Ruben Sadoyan January 12, 2017

Hi all,

I have installed SQL driver for JIRA and now I am able to perform advanced search using SQL.

Two questions:

  1. How can I use SQL queries in JIRA filters and combine them with JQL? 
  2. How can I use JQL query in SQL driver for JIRA console to convert from JQL to SQL?

Thanks,

Aquarius.

3 answers

1 accepted

1 vote
Answer accepted
Ruben Sadoyan January 19, 2017

Thank you Pablo smile

Do you by chance have an SQL query for issues added to the sprint after it started?

 

Thx,

Aquarius.

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 20, 2017

Not yet, but I'm working to extend the SQL for JIRA - Data Analysis console to Agile, so it will possible to analyze Agil boards data out-of-the-box in a very powerful and friendly way to answer to question like this....

0 votes
Ruben Sadoyan January 19, 2017

Thank you very much Pablo, 

 

but when I run 

issues in sql("
select i.key from issues i inner join issuecomments c on c.issueid=i.id where i.jql='reporter=currentUser()' group by i.key having count(*) >= 5
")

in issue navigator I get

Field 'issues' does not exist or you do not have permission to view it.

What is wrong?

 

Thanks in advance,

Aquarius.

 

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 19, 2017

It is a typo: issues ( plural ) is not supported in JQL. Use please:

issue in ...

0 votes
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 19, 2017

Both questions seems to be related and are supported in SQL for JIRA Driver

Staring with your second question, the short answer is YES.

The ISSUES table has a special column named JQL. You have to use it as input to populate the ISSUES table with the data. Example, you might want to get your reported issues which have generated certain amount of debate/comments with threshold 5 (at least 5 comments)

select i.key as "Issue", count(*) as "Comments" from issues i inner join issuecomments c on c.issueid=i.id where i.jql='reporter=currentUser()' group by i.key having count(*) >= 5

copy and paste the query above in the SQL console and perform it to see the results.

In regard of your first question, any SQL for JIRA query can be converted back into a JQL by using the built-in sql predicate. Example:

issues in sql("
select i.key from issues i inner join issuecomments c on c.issueid=i.id where i.jql='reporter=currentUser()' group by i.key having count(*) >= 5
")

The JQL above can be saved as JIRA filter: "Top commented issues" and the use it as any other regular saved filter in JQL by using the filter keyword:

filter="Top commented issues"

 

 

 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events