Custom Fields Missing From ISSUE Table
Question
Why are custom fields not synced to the ISSUE table?
Answer
Starting September 10, 2020, the ISSUE table only has standard fields. See the FIELD, ISSUE_FIELD_HISTORY, and ISSUE_MULTISELECT_HISTORY tables for custom field data.
You can use the following query for custom non-array fields:
SELECT
a.id AS issue_id,
a.key,
c.name AS field_name,
CASE
WHEN c.dimension_table = 'field_option' THEN d.name
ELSE b.value
END AS field_value
FROM JIRA.ISSUE a
LEFT JOIN JIRA.ISSUE_FIELD_HISTORY b ON a.id = b.ISSUE_ID
LEFT JOIN JIRA.FIELD c ON b.FIELD_ID = c.id
LEFT JOIN JIRA.FIELD_OPTION d ON c.dimension_table = 'field_option' AND b.value = CAST(d.id AS STRING)
WHERE c.name = '{{custom_non_array_field_name}}'
ORDER BY field_value;
You can use the following query for custom array fields:
SELECT
a.id AS issue_id,
a.key AS issue_key,
c.name AS field_name,
CASE
WHEN c.dimension_table = 'field_option' THEN d.name
ELSE b.value
END AS field_value
FROM FIVETRAN_DB.JIRA.ISSUE a
LEFT JOIN FIVETRAN_DB.JIRA.ISSUE_MULTISELECT_HISTORY b ON a.id = b.ISSUE_ID
LEFT JOIN FIVETRAN_DB.JIRA.FIELD c ON b.FIELD_ID = c.id
LEFT JOIN FIVETRAN_DB.JIRA.FIELD_OPTION d ON c.dimension_table = 'field_option' AND b.value = CAST(d.id AS STRING)
WHERE c.name = '{{custom_array_field_name}}'
ORDER BY a.id, c.name;