如何在Snowflake中拆分JSON Variant列并提取xyz_id字段

你的Snowflake数据表中列D的结构比较特殊：它是Variant类型的数组，数组内的唯一元素是JSON数组的字符串形式（被双引号包裹）。要提取xyz_id并创建列E，可以通过以下SQL实现：

1. 先查询验证结果 #

SELECT
    D,
    PARSE_JSON(D[0])[0]:xyz_id::INT AS E
FROM your_table;

2. 永久新增列E #

如果需要在表中永久添加列E，执行：

ALTER TABLE your_table
ADD COLUMN E INT AS (PARSE_JSON(D[0])[0]:xyz_id::INT);

各部分作用说明： #

• D[0]：从Variant数组中取出第一个元素（即带引号的JSON数组字符串）
• PARSE_JSON(D[0])：将字符串解析为Variant类型的JSON数组
• [0]:xyz_id：从JSON数组中取出第一个对象，提取其中的xyz_id字段
• ::INT：将提取的数值转换为整数类型（可根据实际需求调整为FLOAT等其他类型）

异常处理（可选） #

如果存在数据格式不规范的情况，建议用TRY_PARSE_JSON避免解析失败导致查询报错：

ALTER TABLE your_table
ADD COLUMN E INT AS (TRY_PARSE_JSON(D[0])[0]:xyz_id::INT);

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