Using a string concatenation to filter labels

Yvette Nash May 9, 2024

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}))

1 answer

1 accepted

0 votes
Answer accepted
Inder Singh
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 10, 2024

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

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

Thanks,

Inder

Yvette Nash May 10, 2024

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 10, 2024

@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 May 10, 2024

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.

Like Inder Singh likes this
Inder Singh
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 10, 2024

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 May 17, 2024

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 21, 2024

@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 May 21, 2024

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events