Finding a jira issue field that does not exist for an issue

Sarah Burgess [X]

Hi team,

I have a custom field that is not always populated on an issue. I would like to report on cases where the field does not exist for an issue.

I have a query that works out and displays the count of each jira_issue_field value, but can't work out how to add "this is not populated for this issue". Any ideas?

This is my current query:

SELECT jira_issue_field.value AS Value,
COUNT(DISTINCT jira_issue.issue_key) AS `Count of ToD`
FROM jira_issue_field
INNER JOIN jira_issue ON jira_issue_field.issue_id = jira_issue.issue_id
INNER JOIN jira_project ON jira_issue.project_id = jira_project.project_id
WHERE (jira_issue_field.name = 'Taxonomy of Demand')
AND {PROJECT_ID.IN('jira_project.project_id')}
AND jira_issue.status = 'Done'
AND jira_issue.resolution_at >= TIMESTAMP({CALENDAR.START})
AND jira_issue.resolution_at <= (TIMESTAMP({CALENDAR.END}) + INTERVAL 1 DAY)
AND jira_issue.resolution_at IS NOT NULL
GROUP BY jira_issue_field.value
ORDER BY COUNT(DISTINCT jira_issue.issue_key) ASC
LIMIT 1000;

t 

Tracy Chow_f2gtzj

Hi @Sarah Burgess [X],

The jira_issue_field table only contains rows for Jira issues that contain values for the custom field. So in order to display the field for issues where the custom field is not populated, you will need to use two queries.

For example, Query 1 would return a list of all issue keys, and Query 2 would return a list of all issue keys and their values for the custom field. When you left join Query 2 to Query 1, then all issues will be displayed whether or not they have a value for that custom field.

Here is a simple sample query in SQL:

WITH query1 AS (
SELECT issue_key
FROM jira_issue
INNER JOIN jira_project ON jira_issue.project_id = jira_project.project_id
WHERE {PROJECT_ID.IN('jira_project.project_id')}),

query2 AS 
(SELECT jira_issue.issue_key,
        jira_issue_field.value
FROM jira_issue 
INNER JOIN jira_issue_field ON jira_issue_field.issue_id = jira_issue.issue_id
WHERE (jira_issue_field.name = 'Taxonomy of Demand))
  
SELECT query1.issue_key,
       query2.value
FROM query1
LEFT JOIN query2 ON query1.issue_key = query2.issue_key

This can also be done by merging two Visual mode queries together. You can select the columns and filters and then change the join type to 'left'.

After getting the query results (in either SQL mode or Visual mode) which would be a list of all issue keys and their custom field values, you can use a "Group & aggregate" step to then get a count of distinct issues.

To replace the null values with text such as "this is not populated for this issue", you can then apply a case statement using an "Apply formula" step on the Value column.

Please let me know if this helps or if you have further questions!