Issue Custom Fields

ISSUECUSTOMFIELDDEFINITIONS

ISSUECUSTOMFIELDDEFINITIONS
Column	ID	NAME	TYPE	DESCRIPTION
Type	BIGINT	VARCHAR	VARCHAR	VARCHAR
Indexed	x	x

ISSUECUSTOMFIELDVALUES

ISSUECUSTOMFIELDVALUES
Column	ISSUEID	CUSTOMFIELDID	VALUE
Type	BIGINT	BIGINT	VARCHAR
Indexed	x	x

Permissions

Since the 5.2.3 version only Jira administrators can list all the custom fields defined on Jira and their descriptions.

Regular Jira users can only view names and ids of a given custom field only.

The two tables above replace the older ISSUECUSTOMFIELDS table. The newer tables bring a lot of benefits:

Better performance
Dynamic (it is no longer required re-start the plugin if a new custom field is added, modified or removed by JIRA or a third party plugin)
Custom fields' original names preserved

Since the 6.1.0 version a new Custom Fields - related table is supported: ISSUECUSOMTFIELDOPTIONS

ISSUECUSTOMFIELDOPTIONS

ISSUECUSTOMFIELDOPTIONS
Column	CUSTOMFIELDID	OPTIONID	OPTIONDISABLED	OPTIONVALUE	SUBOPTIONID	SUBOPTIONDISABLED	PARENTOPTIONID	SUBOPTIONVALUE
Type	BIGINT	BIGINT	BOOLEAN	VARCHAR	BIGINT	BOOLEAN	BIGINT	VARCHAR
Indexed	x

It allows to read all the possible values for custom fields based on lists.

Example 1

Getting all the custom fields

select 
     KEY(JQL.ISSUE) as "Issue", 
     cfd.NAME as "Custom field", 
     cfv.VALUE as "Custom field value" 
from 
     JQL
         inner join
     ISSUECUSTOMFIELDVALUES cfv on cfv.ISSUEID = JQL.ISSUEID
         inner join
     ISSUECUSTOMFIELDDEFINITIONS cfd on cfd.ID = cfv.CUSTOMFIELDID 
where 
     JQL='reporter=currentUser()'

Example 2

Old style

The query above returns one record per custom field. You might want to build a report with one only record (line) per issue and each custom field as column (this is the way that the deprecated ISSUECUSTOMFIELDS table worked). You might want to report issues in JIRA Agile/Software displaying two custom fields as columns: Sprint (10005) and Story Point(10100), then the ISSUUECUSTOMFIELDVALUES has to be joined one per custom field:

select
       key(JQL.issue) as "Issue",
       cfv1.value as "Sprint",
       cfv2.value as "Story Point" 
from 
       JQL
           join 
       issuecustomfieldvalues cfv1 on cfv1.customfieldid = 10004 and cfv1.issueid = JQL.issueid 
           join 
       issuecustomfieldvalues cfv2 on cfv2.customfieldid = 10006 and cfv2.issueid = JQL.issueid
where 
      JQL.query ='reporter = currentUser()'

Note please, that the query uses LEFT (OUTER) JOIN to include issues with null values for the Sprint or the Story Point and the conditions on the customfieldid indexed column are set on the JOIN condition instead of the WHERE clause.

New style

The query above can be optimized a lot with the CF() function to extract data from the JQL.issue object:

select
       key(JQL.issue) as "Issue",
       cf(JQL.ISSUE, 10004) as "Sprint",
       cf(JQL.ISSUE, 10006) as "Story Point" 
from 
       JQL
where 
      JQL.query ='reporter = currentUser()'

Writing queries independent of the Jira instance

Many organizations have different environments for their life cycle (development, staging, production, etc). And the custom field ids can be different in each instance. However, it is possible to re-write the query above to be use the custom field names instead of their ids. See how:

First, build a view with the ids of each targeted custom field:

select 
    cfd1.id as "Sprint Id", 
    cfd2.id as "Story Points Id"
from 
    issuecustomfielddefinitions cfd1 
       join
    issuecustomfielddefinitions cfd2 
where 
    cfd1.name = 'Sprint' 
       and
    cfd2.name = 'Story Points'

Sprint Id	Story Points Id

Sprint Id	Story Points Id
10004	10006

In the query above the same ISSUECUSTOMFIELDDEFINITONS table is joined twice without any constraint (JOIN... ON). The database engine will perform all the MxN combinations, but in this particular case this is not a problem as it is a 1x1 combination which will perform good.

Now, build a new view as combination of the table above with all the records of your JQL:

select 
        * 
from
	(select 
		cfd1.id as "Sprint Id", 
		cfd2.id as "Story Points Id"
	from 
		issuecustomfielddefinitions cfd1 
		   join
		issuecustomfielddefinitions cfd2 
	where 
		cfd1.name = 'Sprint' 
		   and
		cfd2.name = 'Story Points')
	join
	(select *
	   
	from
		JQL
	where
		JQL.query = 'project SSP'
	)

Sprint Id	Story Points Id	ISSUE

Sprint Id	Story Points Id	ISSUE
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.470+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000ev:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000ev:></customfield_10004_changes:34;2019-01-21t09:18:06.470+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.470+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000en:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000en:></customfield_10004_changes:34;2019-01-21t09:18:06.470+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.469+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000ef:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms,Z> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms,Z> stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000ef:></customfield_10004_changes:34;2019-01-21t09:18:06.469+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.466+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000e7:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000e7:></customfield_10004_changes:34;2019-01-21t09:18:06.466+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.466+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000dz:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000dz:></customfield_10004_changes:34;2019-01-21t09:18:06.466+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.464+01:00;a> stored,indexed,tokenized stored,indexed,tokenized stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000dr:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms>></customfield_10005:0\|i000dr:></customfield_10004_changes:34;2019-01-21t09:18:06.464+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:33;2019-02-04t10:28:06.464+01:00;a> stored,indexed,tokenized> Click the "SSP-17" link and read the description tab of the detail view for more> stored,indexed,tokenized> Click the "SSP-17" link and read the description tab of the detail view for more> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,tokenized<description:to delete="" this="" sample="" project="" _(must="" be="" performed="" by="" a="" user="" with="" administration="" rights)_="" -="" open="" the="" interface="" to="" projects="" page="" using="" keyboard="" shortcut="" 'g'="" then="" and="" typing="" 'projects'="" in="" search="" dialog="" select="" "delete"="" link="" for="" "sample="" scrum="" project"="" to="" workflow="" scheme="" schemes="" 'workflow="" schemes'="" "ssp:="" software="" simplified="" scheme"="" go="" workflows="" 'workflows'="" dialog(_ondemand="" users="" should="" second="" match="" workflows_)="" expand="" "inactive"="" section="" "software="" ssp"="" board="" owner="" of="" or="" an="" administrator)_="" click="" "tools"="" cog="" at="" top="" right=""> stored,indexed,tokenized<pq_support_description:to delete="" this="" sample="" project="" _(must="" be="" performed="" by="" a="" user="" with="" administration="" rights)_="" -="" open="" the="" interface="" to="" projects="" page="" using="" keyboard="" shortcut="" 'g'="" then="" and="" typing="" 'projects'="" in="" search="" dialog="" select="" "delete"="" link="" for="" "sample="" scrum="" project"="" to="" workflow="" scheme="" schemes="" 'workflow="" schemes'="" "ssp:="" software="" simplified="" scheme"="" go="" workflows="" 'workflows'="" dialog(_ondemand="" users="" should="" second="" match="" workflows_)="" expand="" "inactive"="" section="" "software="" ssp"="" board="" owner="" of="" or="" an="" administrator)_="" click="" "tools"="" cog="" at="" top="" right=""> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000dj:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms></customfield_10005:0\|i000dj:></pq_support_description:to></description:to></customfield_10004_changes:33;2019-02-04t10:28:06.464+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:34;2019-01-21t09:18:06.456+01:00;a> stored,omitNorms<customfield_10004_changes:33;2019-02-04t10:28:06.457+01:00;a> stored,indexed,tokenized> Try closing this sprint now> stored,indexed,tokenized> Try closing this sprint now> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000db:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms></customfield_10005:0\|i000db:></customfield_10004_changes:33;2019-02-04t10:28:06.457+01:00;a></customfield_10004_changes:34;2019-01-21t09:18:06.456+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:33;2019-02-04t10:28:06.456+01:00;a> stored,indexed,tokenized> Click "Reports" to view the Burndown Chart> stored,indexed,tokenized> Click "Reports" to view the Burndown Chart> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000d3:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms></customfield_10005:0\|i000d3:></customfield_10004_changes:33;2019-02-04t10:28:06.456+01:00;a></stored,indexed
10004	10006	Document<stored,indexed stored,indexed stored,omitNorms<customfield_10004_changes:33;2019-02-10t15:28:06.455+01:00;a> stored,indexed,tokenized> Try clicking the "Only My Issues" Quick Filter above> stored,indexed,tokenized> Try clicking the "Only My Issues" Quick Filter above> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,tokenized<description:creating quick="" filters="" you="" can="" add="" your="" own="" filters="" in="" the="" board="" configuration="" (select="" board=""> Configure)> stored,indexed,tokenized<pq_support_description:creating quick="" filters="" you="" can="" add="" your="" own="" filters="" in="" the="" board="" configuration="" (select="" board=""> Configure)> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed<customfield_10005:0\|i000cv:> stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed stored,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms stored,indexed,omitNorms></customfield_10005:0\|i000cv:></pq_support_description:creating></description:creating></customfield_10004_changes:33;2019-02-10t15:28:06.455+01:00;a></stored,indexed