BigQuery中如何对数组使用正则表达式匹配元素并生成新字段

实现方案 #

这里提供两种符合要求的实现方式，均基于你提供的测试用例编写，适配支持数组操作与正则匹配的SQL引擎（如BigQuery、Spark SQL、Presto等，函数名存在微小差异可对应调整）。

方案1：数组子查询判断（性能更优） #

核心逻辑：遍历数组元素，用正则^[cd]匹配是否存在以c/d开头的元素，正则中^表示字符串开头，[cd]匹配c或d任意一个字符，满足你的正则+数组/UNNEST配合的要求。
对应SQL代码：

WITH table_1 AS (
    SELECT '1' AS item, ['aa'] AS expression UNION ALL 
    SELECT '2', ['aa', 'ba', 'ca', 'da', 'ea'] UNION ALL 
    SELECT '3', ['aa', 'ba'] UNION ALL 
    SELECT '4', ['aa', 'ba', 'ca'] 
)
SELECT 
    *,
    CASE WHEN EXISTS(
        SELECT 1 FROM UNNEST(expression) elem 
        WHERE REGEXP_CONTAINS(elem, r'^[cd]')
    ) THEN 1 ELSE 0 END AS `exists`
FROM table_1

注：不同引擎的正则匹配函数名略有差异：Spark SQL用rlike、Presto用regexp_like，替换对应函数即可生效。部分引擎支持更简洁的数组匹配函数，比如Presto/Trino可以直接写CAST(any_match(expression, x -> regexp_like(x, '^[cd]')) AS INT) AS exists``，不需要写子查询。

方案2：显式UNNEST后聚合（适合需要同时处理数组元素其他逻辑的场景） #

如果需要配合UNNEST做更多数组元素的加工，可以先拆分数组再聚合判断：

WITH table_1 AS (
    SELECT '1' AS item, ['aa'] AS expression UNION ALL 
    SELECT '2', ['aa', 'ba', 'ca', 'da', 'ea'] UNION ALL 
    SELECT '3', ['aa', 'ba'] UNION ALL 
    SELECT '4', ['aa', 'ba', 'ca'] 
)
SELECT 
    t.item,
    t.expression,
    MAX(CASE WHEN REGEXP_CONTAINS(elem, r'^[cd]') THEN 1 ELSE 0 END) AS `exists`
FROM table_1 t,
UNNEST(t.expression) elem
GROUP BY t.item, t.expression

两种方案的输出结果均符合你给出的预期要求。

内容的提问来源于stack exchange，提问作者Alex