How Can I Query Array Custom Fields?

Question

How can I query array custom fields?

Environment

Connector: Jira

Answer

To query array custom fields, do the following:

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';