在BigQuery中获取分隔数组里指定元素的下一个值

需要处理以->为分隔符的字符串，将其转换为数组后，获取字符串中第一个出现的"Res"之后的第一个非"Res"元素，示例如下：

• 输入："Q -> Res -> tes -> Res -> twet"，期望输出：tes
• 输入："rw -> gewg -> tes -> Res -> twet"，期望输出：twet
• 输入："Y -> Res -> Res -> Res -> twet"，期望输出：twet

原尝试代码Array_reverse(split(regexp_extract(COLUMN_NAME, '(.*?)Res'), '->'))[safe_offset(1)]得到的是"Res"之前的元素，需要修正实现逻辑。

以下是针对需求的正确实现，核心逻辑是先清洗分割后的数组元素，再定位第一个"Res"的位置，最后筛选出该位置后第一个非"Res"的元素：

WITH sample_data AS (
  SELECT "Q -> Res -> tes -> Res -> twet" AS target_str UNION ALL
  SELECT "rw -> gewg -> tes -> Res -> twet" AS target_str UNION ALL
  SELECT "Y -> Res -> Res -> Res -> twet" AS target_str
)
SELECT
  target_str,
  -- 获取目标结果
  (
    SELECT trimmed_element
    FROM UNNEST(ARRAY(SELECT TRIM(x) FROM UNNEST(SPLIT(target_str, '->')) x)) trimmed_element WITH OFFSET pos
    -- 筛选位置在第一个"Res"之后，且元素不是"Res"的记录
    WHERE pos > (
      SELECT MIN(pos)
      FROM UNNEST(ARRAY(SELECT TRIM(x) FROM UNNEST(SPLIT(target_str, '->')) x)) elem WITH OFFSET pos
      WHERE elem = 'Res'
    )
    AND trimmed_element != 'Res'
    -- 取第一个符合条件的元素
    LIMIT 1
  ) AS result
FROM sample_data

1. 分割并清洗数组：用SPLIT(target_str, '->')分割字符串，再通过TRIM(x)去除每个元素前后的空格，得到干净的数组。
2. 定位第一个"Res"的位置：通过子查询找到数组中第一个"Res"出现的最小偏移量MIN(pos)。
3. 筛选目标元素：在数组中找到位置大于第一个"Res"位置、且元素不为"Res"的第一条记录，即为所需结果。

如果你的实际需求是获取最后一个"Res"之后的元素（比如行1期望输出twet），可以修改为以下代码：

WITH sample_data AS (
  SELECT "Q -> Res -> tes -> Res -> twet" AS target_str UNION ALL
  SELECT "rw -> gewg -> tes -> Res -> twet" AS target_str UNION ALL
  SELECT "Y -> Res -> Res -> Res -> twet" AS target_str
)
SELECT
  target_str,
  (
    SELECT trimmed_element
    FROM UNNEST(ARRAY(SELECT TRIM(x) FROM UNNEST(SPLIT(target_str, '->')) x)) trimmed_element WITH OFFSET pos
    WHERE pos = (
      SELECT MAX(pos) + 1
      FROM UNNEST(ARRAY(SELECT TRIM(x) FROM UNNEST(SPLIT(target_str, '->')) x)) elem WITH OFFSET pos
      WHERE elem = 'Res'
    )
  ) AS result
FROM sample_data

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