JQL request: Extract Field value rather than Issues

[Thierry Molina]

Hello,

If I correctly understood, JQL are done to extract tuples (that is to say "issues") from the database. But I would like to know if it is possible to return only a field from each issue.

Example:

JQL request : type = Bug

seems to be equivalent to an SQL request:

SELECT * FROM table WHERE type = Bug

but what should I do if I just want a list of status of these issues?

the JQL page says:

"JQL gives you some SQL-like syntax, such as the ORDER BY SQL keyword and ISNULL() SQL function (i.e. the NULL keyword in JQL). However, JQL is not a database query language. For example, JQL does not have a SELECT statement."

So I think this is not possible to make this with a JQL request. Can you first confirm that?

Actually, I would like to find the average time the issues (of a project or a filter) are Open. So I think it's possible to create a custom scripted field containing the total time an issue is Open. Now I want to knnow how to add all values from this field and make an average from them.

Thank you if you read me until the end, I know it is not easy.

[Nic Brough -Adaptavist- · Accepted solution]

Correct. JQL returns a list of issues, not their data.

Actually, in the background, it's a bit more complex than that, and if you're coding, it is possible to execute JQL that returns subsets of data instead of just swathes of issues, but it has no huge advantages other than saving memory (good if you have large Jira installations and very wide ranging searches), and it's a bit more complex to use than just "Search, get a list, iterate through issues"

I'm not sure it would be of much use to you either - there's no field for "total time issue is open", your code is going to need to read the history of each issue from the filter and work it out from there.

[Thierry Molina]

Thank you for your fast answer.

Ok so you confirm the first point thank you.

I know there is no field for "total time issue is open" but that is why this field mut be created into a custom scripted field . Like that:

https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+Fields#ScriptedFields-TotaltimethisissuehasbeenInProgress

... but for the "Open" status.

And that is after having created this field that I don't know how retrieve this field for all issues of a project, and then make an average, or a chart for example like that:

http://www.hostingpics.net/viewer.php?id=575913timetoresolveissues.png

[Nic Brough -Adaptavist-]

Yes, that's what I was getting at - there's no field for time-spent, so your scripted field won't be able to take advantage of field selection at a JQL level - it needs to get the issues and do some cacluations on them.

I'd have suggested the script runner for your field, but you got there before me.

How you "retreive a field" depends heavily on what you are running and where. You'll need to write something to get a graph like that. Have you tried the time-to-resolve gadget though?

[Thierry Molina]

OK, sorry if I didn't understood something.

Yes, I see what you mean, we can't make calculation with JQL request because field values can't be requested only.

The gadget "Time resolution" give the inormation but during a period. I'm searching for a kind of snapshot, today and since the beginning, and make range by "number of days Open", exactly like my link, this chart is simply done with Excel. It is the same inconvenient with the gadget "Average time in status", the abscisse is always the time. And I think it will take too much of time to develop our own gadget

I think I will follow this track:

- create the custom scripted field or use the "Time in status" field already existing

- make a script (PHP for example) to grab values of this field with a MySQL request (meanwhile I learned that our JIRA was based on MySQL) and export them into a CSV file.

- then easily make a bar graph

(And we need to make other statistics so I think it will be necessary to master the DB anyway, it will be powerfull to use it directly rather than using JIRA)

Thank you for your answer and your help

[Pablo Beltran]

Use SQL for JIRA. It transforms any JQL into a SQL.