如何在BigQuery中使用正则提取清洗多格式字段的目标值

BigQuery字段双格式值提取方案 #

调整正则规则将字典结构的前后匹配段设置为可选匹配，即可同时覆盖纯文本、类字典字符串两种格式的名称提取，无需编写多分支判断逻辑。

可直接使用的提取语句 #

在REGEXP_EXTRACT函数中使用如下正则即可完成全量数据清洗：

REGEXP_EXTRACT(你的目标字段名, r"(?:{'name': ')?([^'}]+)(?:'(?:, 'url': .*})?)?$")

正则逻辑说明 #

• (?:{'name': ')?：非捕获匹配字典格式的开头前缀，?标记表示该段可有可无，适配纯文本无此前缀的场景
• ([^'}]+)：核心捕获组，匹配所有非单引号、非右大括号的连续字符，既可以完整提取纯文本内容，也可以精准截取字典中name对应的value值，不会出现贪婪匹配到url字段的问题
• (?:'(?:, 'url': .*})?)?$：非捕获匹配字典格式的后缀部分（单引号+url字段+右大括号），同样加?标记表示该段可有可无，结尾$锚定字符串末尾，避免异常内容干扰匹配结果

匹配效果验证 #

两种格式输入的提取结果完全符合预期：

• 输入纯文本Health and Medicines，输出Health and Medicines
• 输入字典格式字符串{'name': 'Health and Medicines', 'url': 'https://www.testurl.com/c/health-medicine'}，输出Health and Medicines

如果提取结果存在前后冗余空格，可以在函数外层套TRIM()做二次清理即可。

内容的提问来源于stack exchange，提问作者Cristian Avendaño