如何使用Snowflake的REGEXP_SUBSTR提取Variant列中的ItemCode值

方案1：优先使用原生JSON处理函数（更稳定，无需正则） #

Snowflake对Variant类型的JSON数据有原生支持，直接操作字段比正则解析容错性更高，不会因为JSON格式换行、空格变化出现匹配失败问题：

-- 提取所有ItemCode拼接为数组
SELECT
    ARRAY_AGG(json_item.value:ItemCode::VARCHAR) AS all_item_codes
FROM 你的表名
    ,LATERAL FLATTEN(input => 你的Variant列名) json_item
GROUP BY 你的表主键字段;

-- 如果你需要把所有ItemCode用逗号拼接为字符串，把ARRAY_AGG替换为LISTAGG即可：
-- LISTAGG(json_item.value:ItemCode::VARCHAR, ',') AS all_item_codes

方案2：使用REGEXP_SUBSTR正则提取 #

如果确实需要用正则函数实现，写法如下：

• 提取第一个匹配的ItemCode值：

SELECT REGEXP_SUBSTR(
    你的Variant列名::VARCHAR,
    '"ItemCode":"([a-zA-Z0-9]+)"', -- 匹配ItemCode键值对，捕获字母数字组成的值
    1, -- 起始匹配位置
    1, -- 取第1个匹配结果
    'e', -- 参数指定返回捕获组内容
    1 -- 返回第1个捕获组的内容
) AS first_item_code
FROM 你的表名;

• 提取所有匹配的ItemCode值，返回为数组：

SELECT REGEXP_SUBSTR_ALL(
    你的Variant列名::VARCHAR,
    '"ItemCode":"([a-zA-Z0-9]+)"',
    1,
    'e',
    1
) AS all_item_codes
FROM 你的表名;

注意：正则方案仅适用于JSON格式固定、ItemCode值仅为字母数字且无转义字符的场景，存在特殊字符时会出现匹配错误，优先使用原生JSON方案。

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