如何在Snowflake中提取非标准格式字符串中的ID值

你存储的是**Ruby哈希（Hash）**格式，不是标准JSON，所以parse_json会报错。需要先将其转换为标准JSON，再解析提取字段。

1. 转换Ruby哈希为标准JSON #

通过多层字符串替换修正语法：

SELECT
  -- 替换Ruby的键格式 :key=> 为JSON的 "key":
  REPLACE(
    -- 替换Ruby的nil为JSON的null
    REPLACE(
      -- 给无引号的日期字符串加引号（匹配Mon, 15 May 2023这类格式）
      REGEXP_REPLACE(
        your_column_name,
        '(Mon|Tue|Wed|Thu|Fri|Sat|Sun), \d{1,2} \w{3} \d{4}',
        '"\\0"'
      ),
      'nil',
      'null'
    ),
    '=>',
    ':'
  ) AS converted_json
FROM your_table;

2. 解析JSON并提取ID字段 #

基于转换后的JSON，直接提取目标字段：

SELECT
  -- 转换并解析JSON
  parse_json(
    REPLACE(
      REPLACE(
        REGEXP_REPLACE(
          your_column_name,
          '(Mon|Tue|Wed|Thu|Fri|Sat|Sun), \d{1,2} \w{3} \d{4}',
          '"\\0"'
        ),
        'nil',
        'null'
      ),
      '=>',
      ':'
    )
  ) AS parsed_json,
  -- 提取old_id的id值
  parsed_json:old_id.id::VARCHAR AS old_id,
  -- 提取new_id的id值（nil转null后会显示为Snowflake的NULL）
  parsed_json:new_id.id::VARCHAR AS new_id
FROM your_table;

容错优化 #

如果存在格式不统一的情况，用try_parse_json避免报错，同时用IFNULL处理空值显示：

SELECT
  try_parse_json(
    REPLACE(
      REPLACE(
        REGEXP_REPLACE(
          your_column_name,
          '(Mon|Tue|Wed|Thu|Fri|Sat|Sun), \d{1,2} \w{3} \d{4}',
          '"\\0"'
        ),
        'nil',
        'null'
      ),
      '=>',
      ':'
    )
  ) AS parsed_json,
  IFNULL(parsed_json:old_id.id::VARCHAR, '无值') AS old_id,
  IFNULL(parsed_json:new_id.id::VARCHAR, '无值') AS new_id
FROM your_table;

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