如何在Snowflake中提取JSON数组中以52或44开头的值到不同列

问题分析 #

你提供的json字段并非标准JSON格式，而是自定义结构化字符串，需要从中提取所有以52或44开头的6位数字，分别存入first_pick和second_pick列。原SQL存在两个核心问题：

1. WHERE条件逻辑错误：json LIKE '52%' or '44%'会被解析为(json LIKE '52%') OR TRUE，导致筛选失效；
2. 误用JSON函数：输入字符串不符合JSON规范，JSON_EXTRACT_PATH_TEXT无法正常工作。

解决方案SQL #

SELECT 
    json,
    -- 提取第一个符合条件的6位数字（52/44开头）
    REGEXP_SUBSTR(json, '\\b(52|44)\\d{4}\\b', 1, 1) AS first_pick,
    -- 提取第二个符合条件的6位数字，无匹配则返回NULL
    REGEXP_SUBSTR(json, '\\b(52|44)\\d{4}\\b', 1, 2) AS second_pick
FROM x
WHERE date = '2023-07-08'
  -- 修正筛选条件，确保只匹配开头为52或44的行
  AND (json LIKE '52%' OR json LIKE '44%');

代码说明 #

• \\b：匹配单词边界，避免从更长的数字串中误提取子串（比如避免匹配1520526中的520526）；
• (52|44)\\d{4}：精准匹配以52或44开头、后续跟4位数字的6位目标值；
• REGEXP_SUBSTR的第4个参数指定匹配序号：1取第一个匹配项，2取第二个，无匹配时返回NULL。

内容的提问来源于stack exchange，提问作者Биляна Митова