BigQuery中从多行字符串提取多个匹配System值的实现方法咨询

解决方案 #

你可以直接使用BigQuery内置的REGEXP_EXTRACT_ALL函数完成需求，该函数可以提取字符串中所有符合正则规则的匹配项，返回数组后通过UNNEST展开即可得到你要的结果，无需提前预知System条目的数量。

完整查询代码 #

SELECT
  `key`,
  system_content AS System
FROM
  my_table
CROSS JOIN UNNEST(
  REGEXP_EXTRACT_ALL(description, r'System: ([^\r\n]*)')
) AS system_content

代码说明 #

• 正则规则r'System: ([^\r\n]*)'中，[^\r\n]*会匹配除换行、回车符之外的所有字符，精准提取每行System: 后面到行尾的所有内容，不会跨行匹配，完美适配你的多行字符串场景
• REGEXP_EXTRACT_ALL会把同一行description中所有符合要求的内容全部提取为数组，不管有多少个System条目都能全覆盖
• CROSS JOIN UNNEST会把数组展开为多行，自动关联对应的key，输出结果和你要求的格式完全一致

如果你需要保留没有System条目的key，可以把CROSS JOIN替换为LEFT JOIN，没有匹配结果时System字段会返回NULL。

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