Jira data model to find custom fields

[Matebese, S. (Sithembiso)]

I'm using SQL server to find custom fields for our tabular reporting and I've struggling to find a list of these in the CUSTOMFIELDOPTION table by using the following queries:

select customfield from customfieldvalue cfv
where cfv.customfield = (select id from customfield where cfname = 'Epic Link') ;

select [CUSTOMFIELD] from CUSTOMFIELDOPTION
where customfield = (select id from customfield where cfname = 'Epic Link') ;

The list of custom fields I'd like to get:

'Acceptance Criteria'
'COD'
'Customer Request Type'
'Dependency'
'Epic Benefit'
'Epic Link'
'Epic Net Present Value'
'EPIC Non incremental benefits'
'Estimated Cost of Epic'
'Funding UID'
'Job Size'
'Label'
'Priority'
'Project Name'
'Release Value'
'Risk Reduction/ Opportunity Enablement'
'Status Category'
'Sub-Task'
'Theme/Capability'
'Time Criticality'
'UID Benefit'
'UID Cost'
'UID Net Present Value'
'User Value'
'WSJF Benefit hypothesis'
'Go-live Date'
'ETE Date'
'QA Date'

Kindly assist on how I can find these custom fields in the CUSTOMFIELDOPTION table.

[Manuel Galván · Accepted solution]

Try this query (is in postgreSQL but you can adapt it):

select cfo.customvalue from customfieldoption cfo, customfield cf
where cf.cfname = 'customfieldName' and cf.id=cfo.customfield

[Nic Brough -Adaptavist- · Accepted solution]

Ah, the joys of trying to use a Jira database for reporting (hint - don't, it's horrible)

For a list of custom fields, look at Customfield.  Forget customfieldoptions, that's for holding the things you see in select/checkmark fields as options for selection.

[Matebese, S. (Sithembiso)]

Thanks, where else can I get the contents you see in the select / checkmark fields? Because our reporting requires them too. Here's an example of our queries:

Select

[cfo].customvalue As [APIApplication],

Convert(Int,[cfv].[Issue]) As [IssueID],

[cf].cfname As [Description]

From [customfield] As [cf]

Inner Join [customfieldvalue] As [cfv]

On

[cf].[ID] = [cfv].CustomField

inner join customfieldoption As [cfo]

On

[cfo].[CUSTOMFIELD] = cf.[ID]

and

Convert(varchar,[cfo].ID) = cfv.STRINGVALUE

Where [cf].cfname = rtrim('API Application')

[Nic Brough -Adaptavist-]

You didn't ask for that, you asked for a list of fields.

If you want the contents (names of options I'm guessing), you'll need to join jiraissue and customfieldvalue, and then customfield option on each result you get.

Seriously, stop using the database for this, it's horrible.

[Matebese, S. (Sithembiso)]

Ok thanks @Nic Brough -Adaptavist-

I can notice I wasn't clear. So I can get the customfield names in the CUSTOMFIELD table and now I'm looking for their respective contents on the CUSTOMFIELDOPTION table.

I get your point about not using the Jira database for reporting, it's very intensive.

[Manuel Galván]

@Matebese, S. (Sithembiso)  Do you need custom fields of a issue with their values or only custom fields with all posible values (for example all options of a list custom field)?

[Nic Brough -Adaptavist-]

Yes, it's clear what you're doing, as I said, you'll need to join jiraissue and customfieldvalue, and then customfield option on each result you get.

[Matebese, S. (Sithembiso)]

@Manuel GalvánI need the custom fields of a Issue with the respective values from CUSTOMFIELDOPTION table.

So the SQL query example I have is like:

get the ID from CUSTOMFIELD and use that ID to get the ISSUE from CUSTOMFIELDVALUE and CUSTOMVALUE from CUSTOMFIELDOPTION

[Manuel Galván]

@Matebese, S. (Sithembiso)  this query returns all fields of an issue with option values:

select ji.pkey,ji.issuenum,cf.cfname, cfo.customvalue cfvalue
from customfieldoption cfo,
customfield cf, customfieldvalue cfv, jiraissue ji
where cf.id=cfo.customfield and
cfv.customfield=cf.id and ji.id="issueid" and
cfv.issue=ji.id and cfv.stringvalue=cast(cfo.id as varchar)

[Matebese, S. (Sithembiso)]

Thank you