nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Inder Singh

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

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

Thanks,

Inder

All comments

Inder Singh

@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.