调整Snowflake的REGEXP_SUBSTR提取JSON中的customer_id

调整后的REGEXP_SUBSTR查询语句 #

针对你的需求，调整后的SQL语句可实现存在customer_id时返回对应值，不存在时返回0：

SELECT 
    *,
    COALESCE(REGEXP_SUBSTR(social_field, '"customer_id":"([^"]+)"', 1, 1, 'e', 2), '0') AS cust_id
FROM emp_data;

调整说明 #

1. 正则表达式修正：原语句使用"（HTML转义的双引号），若social_field存储的是原生JSON格式（直接用双引号），则无法匹配。改为直接使用"，适配标准JSON的引号格式。
2. 空值处理：用COALESCE函数将正则匹配失败返回的NULL转换为'0'，满足不存在时显示0的要求。

适配更复杂场景的扩展语句 #

如果customer_id的值可能是数字类型（无双引号包裹），或者字段内存在空格，可进一步调整正则表达式：

SELECT 
    *,
    COALESCE(REGEXP_SUBSTR(social_field, '"customer_id":\\s*"?([^",]+)"?', 1, 1, 'e', 2), '0') AS cust_id
FROM emp_data;

该正则的关键优化点：

• \\s*：匹配customer_id冒号后可能存在的空格
• "?：匹配可选的双引号，同时适配字符串型和数字型的customer_id
• ([^",]+)：匹配除双引号、逗号之外的所有内容，覆盖多数常见值格式

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