Need postgre query to get list of users involved in specific jira ticket issue

[devteam]

Pls provide postgres query to get list of users involved in specific jira ticket issue

[Nic Brough -Adaptavist-]

You don't have access to the database on Cloud (as you've tagged this), so there's no point in asking technically.

But if you actually mean "server", then the simple answer is "don't".  The Jira database is absolutely unfit for any form of reporting, and you probably don't want to have to learn how to build all the torturous SQL you'll need to be able to get the data out (leading to problems such as the wrong answers coming out, or even causing problems for active users as the SQL is non-performant).

That warning aside, next you need to clarify the question - what does "involved" mean?  Reporter, Creator, and Assignee, are probably the obvious ones, but there are loads of others - people who have commented?  Logged time on it?  Named in a user-picker? Worked in it in Bitbucket?  Edited?  Linked?  Mentioned?  Been part of the team that performed the sprint it got completed in?  People who transitioned it?

As you can imagine, all of those are held in different ways in the database, with you needing at least two joins for all but Reporter, Creator and Assignee.

However, if you read the issue over the REST API, you won't need to do any SQL, you'll get JSON that answers all of that in one go (except mentions without a little bit of parsing), and it'll work on Server and Cloud.

[devteam]

Actualy I have jira trial installation locally and need postgres query to get users who worked on the ticket(s) listed, eg, assigned to, commented, or changed status.

I have postres configured to link with jira .So just need help with query

Thats it

[Nic Brough -Adaptavist-]

Right, you'll need to work on a replicated copy of the database to avoid damaging it (or take Jira offline while you do this)

You'll need to read the jiraissue, changeitem, changehistory, worklog, jiraaction, customfieldvalue, customfield, and join bits of them to cwd_user.

Please, don't, you're just making yourself problems.

[devteam]

glad if can share the postgres query .I will be very grateful.I am not good with postgres queries.

Thanks