Presto中正则提取匹配指定JSON前缀的ID字段问题求解

问题背景 #

• 待处理列数据格式为[ID]={JSON字符串}，示例值：a35={"abc":"D1,9,12, 23, 24, 25, 26"}
• 需求：仅当JSON内abc字段值以D1开头时，提取等号左侧的ID作为新列
• 原有正则写法无返回结果，测试代码如下：

--sample data
WITH dataset(id_str) AS (
  SELECT ('a35={"abc":"D1,9,12, 23, 24, 25, 26"}')
)

--query
SELECT regexp_extract_all(id_str, '"\b(?<id>\w{3})\=\{\"abc\"\:\"D1\,"')
FROM dataset;

原写法失效原因 #

• 正则开头冗余添加了引号匹配规则（原代码写的HTML转义字符"），实际字符串起始位置为ID字符，从第一步就匹配失败
• Presto正则引擎不需要自定义命名捕获组(?<id>...)，直接按捕获组位置取值即可
• 正则转义逻辑冗余，等号、逗号不属于正则特殊字符，不需要额外加反斜杠转义；同时Presto字符串内写正则转义时，反斜杠需要双写

可行方案 #

方案1：正则快速匹配（适合数据格式完全固定的场景） #

直接简化正则规则，用regexp_extract提取第一个捕获组的ID内容即可，匹配失败会返回空字符串，过滤空值即可得到符合要求的结果：

WITH dataset(id_str) AS (
  SELECT 'a35={"abc":"D1,9,12, 23, 24, 25, 26"}'
)
SELECT 
  regexp_extract(id_str, '^(\w+)=\{"abc":"D1', 1) AS extracted_id
FROM dataset
WHERE extracted_id != ''

正则逻辑说明：从字符串起始位置匹配连续的字母/数字/下划线（即ID部分），校验后续内容是否匹配固定前缀={"abc":"D1，匹配成功则返回捕获到的ID。

方案2：拆分+JSON解析（鲁棒性更强，生产环境推荐） #

如果数据可能存在JSON前后多余空格、字段顺序变动的情况，不要依赖硬编码正则，用Presto内置的字符串拆分、JSON函数处理，准确率更高：

WITH dataset(id_str) AS (
  SELECT 'a35={"abc":"D1,9,12, 23, 24, 25, 26"}'
)
SELECT 
  split_part(id_str, '=', 1) AS extracted_id
FROM dataset
WHERE 
  -- 拆分出等号右侧的JSON内容，提取abc字段的字符串值
  -- 判断字段值是否以D1开头
  starts_with(json_extract_scalar(split_part(id_str, '=', 2), '$.abc'), 'D1')

注：如果部分记录的JSON中不存在abc字段或字段值为null，json_extract_scalar会返回null，starts_with接收null参数时也会返回null，会被WHERE条件自动过滤，不需要额外写空值判断逻辑。

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