Snowflake中提取JSON对象所有冒号前ID的技术问题

问题场景 #

给定示例JSON字符串：{"198002": 2115960, "198006": 604560, "320906": 302280}，需要提取所有冒号前的ID并以逗号分隔（即198002,198006,320906），原正则方法仅能提取第一个ID，需优化。

方法一：使用Snowflake JSON内置函数（推荐） #

利用Snowflake原生的JSON处理函数，比正则更稳定，适配各种合法JSON格式：

SELECT 
  player_army_load,
  ARRAY_TO_STRING(OBJECT_KEYS(PARSE_JSON(player_army_load)), ',') AS element_id
FROM x;

• PARSE_JSON(player_army_load)：将字符串类型的JSON转为Snowflake JSON对象
• OBJECT_KEYS(...)：提取JSON对象的所有键，返回数组
• ARRAY_TO_STRING(..., ',')：将数组转为逗号分隔的字符串

方法二：改进正则表达式（字符串处理方式） #

如果必须通过字符串正则处理，可调整正则规则实现全局匹配替换：

SELECT 
  player_army_load,
  TRIM(REGEXP_REPLACE(player_army_load, '(:[^,}]+)|[{}"]', ''), ',') AS element_id
FROM x;

• 正则(:[^,}]+)|[{}"]匹配两类内容并替换为空：
  1. :[^,}]+：冒号开始到逗号或右大括号结束的所有字符
  2. [{}"]：大括号和双引号
• TRIM(..., ',')：清理结果首尾可能残留的逗号

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