Using a string concatenation to filter labels

[Yvette Nash]

I would like to concatenate a string from a dashboard text controller (RM_ID in my example) and search for that string in the labels field. I do not know how to use the CONCAT operation within labels or even if I should be using CONCAT at all to get the desired result.

SELECT COUNT(DISTINCT `Issue`.`issue_id`) AS `Count of unique Issue Issue ID`
FROM `jira_issue` AS `Issue`
INNER JOIN `jira_issue_field` AS `Issue field` ON `Issue field`.`issue_id` = `Issue`.`issue_id`
WHERE (`Issue`.`issue_type` = 'Epic')
AND `Issue`.`labels` IN (CONCAT('Added_',{RM_ID}))

[Inder Singh · Accepted solution]

@Yvette Nash : For the WHERE clause, can you try this instead:

WHERE ARRAY_CONTAINS(`Issue`.`labels`, concat("Added_", {RM_ID}))

Thanks,

Inder

[Yvette Nash]

Worked great! Thank you so much again!

Is there a way to add a wildcard in there? The desired effect would be Added_%{RM_ID}%. 

[Inder Singh]

@Yvette Nash : ARRAY_CONTAINS does not support wildcards. So we would need a different query such as one below:

----

with tbl1 as (
    SELECT explode(`Issue`.`labels`) AS `Labels`,
                `Issue`.`issue_id` AS `Issue ID`
   FROM `jira_issue` AS `Issue`
   GROUP BY `Issue`.`labels`, `Issue`.`issue_id`
   ORDER BY `Issue`.`labels` ASC, `Issue ID` ASC
)

SELECT COUNT(DISTINCT `Issue ID`) as issue_count
from tbl1
WHERE Labels LIKE 'Added_%{RM_ID.RAW}%'

-------

[Yvette Nash]

Incredibly helpful Inder! Thank you!

The things I'm trying to do are far more complex that the visual editor allows but your examples and fiddling with what the editor initially creates has been successful.

[Inder Singh]

We recognize that Visual will not suit every use case especially more complex ones, so SQL comes in handy. We also have knowledge base articles that you can refer to https://confluence.atlassian.com/analyticskb/atlassian-analytics-knowledge-base-1142445615.html and they provide helpful tips.

Cheers!

[Yvette Nash]

Still playing with wildcard filtering but this time I'm also looking for a count WITHOUT certain labels. I used this

WHERE (`Issue Type` = 'Epic')
AND (Labels LIKE 'Added_%{RM_ID.RAW}%'OR ARRAY_CONTAINS(`Issue Labels`, concat("RM_", {RM_ID})))
AND Labels NOT LIKE 'Removed_%{RM_ID.RAW}%'

It seems the last clause (NOT LIKE) is completely ignored. I don't get any errors when I run this however. NOTE: an issue can have the RM_{RM_ID} label AND a Removed_%{RM_ID.RAW}% label present but not RM_{RM_ID} and Added_%{RM_ID.RAW}%   

[Inder Singh]

@Yvette Nash Are you are trying to do this after you have already performed explode operation on labels? If yes, then each row now only has a single value for the label and not multiple values (that is what explode does). For example, if an issue has 3 labels attached to it, then after explode, you get 3 rows for the same issue where each row has distinct label.

[Yvette Nash]

Ah! That makes sense now. Thank you for the clarification.

[Jackie Budniak]

@Inder Singh this was very useful.

I was able to use it for a report someone needed on Labels which we were struggling on how to split them.

[Inder Singh]

@Jackie Budniak Great to hear this!