Question

Is it possible to query array custom fields?

Environment

Connector: Jira

Answer

1. Discover the code of the custom field you are looking for with the following query (adjust to your needs):

   SELECT *
FROM DATALAKE.JIRA.FIELD_OPTION fo
WHERE LOWER(fo."NAME") LIKE '%<your_custom_field_display_name>%';

2. Once you know the custom_field ID, use it in the following query to match the field_id and show the actual value for your issues.

   SELECT i.id, i.KEY, fo., ih."TIME", i."_FIVETRAN_SYNCED"
from DATALAKE.JIRA."ISSUE" i
INNER JOIN JIRA.ISSUE_MULTISELECT_HISTORY ih
ON ih.ISSUE_ID = i.ID
INNER JOIN JIRA.FIELD_OPTION fo
ON fo.id = ih.VALUE
WHERE ih.FIELD_ID = 'customfield_XXXX' AND i."KEY" = 'XX-YYYY';