SQL query

[Olexandr Polinkevych]

How to select all issues that in project1 and custom_field = "value" directly from database?
I need it in order to get issues without checking permissions. 
JQL example: project = proj1 and custom_field1 = value

[Andy Heinzer · Accepted solution]

You can probably find this information in SQL with a query like this:

select cfv.issue, cfv.stringvalue, cf.cfname, P.PKey || '-' || issue.IssueNum as IssueKey
from CustomFieldValue cfv 
join CustomField CF on CF.Id = CFV.CustomField
join jiraissue issue on issue.id = cfv.issue
join Project P on issue.Project = P.Id
where P.pkey='SCRUM' and cf.cfname='text1';

This works in postgresql, but the syntax might be different for other database types.  In my case my project key here is called 'SCRUM' and my custom field name is 'text1' (both without quotes.

[Olexandr Polinkevych]

Thank you a lot, that's exactly what i need!

[Piotr Bojko]

@Olexandr Polinkevych what does this mean "without checking permissions"? You want to get a specific data despite in JIRA you do not have access because of permissions to them?

[Olexandr Polinkevych]

Yes, i do.

[Nic Brough -Adaptavist-]

This is a horrible way to get data from Jira, please consider doing it properly instead.

But, if you must, then you will need to join Project (or hard-code the specific project), customfield (or hard-code it again) and customfieldvalue with Jiraissue to find it.  It will also be inaccurate if config has been changed in certain ways, and it will ignore any permissions and security.

[Olexandr Polinkevych]

can you provide an example of query pls?

[Nic Brough -Adaptavist-]

It will be quicker for you to work it out for myself.  I would never do it this way, so I've got nothing to hand.