Snowflake中提取VARCHAR类型JSON列的macAddress字段

核心思路 #

先将带Python风格u'前缀的字符串转换为标准JSON格式，再转为VARIANT类型，最后通过扁平化操作提取目标字段。

具体SQL实现 #

假设你的表名为your_table，直接执行以下查询即可得到所需结果：

SELECT 
    flattened.value:macAddress::VARCHAR AS mac_address
FROM 
    your_table,
    LATERAL FLATTEN(INPUT => PARSE_JSON(REPLACE(ports, 'u\'', '\''))) AS flattened;

步骤拆解 #

• 清理非标准JSON格式：用REPLACE(ports, 'u\'', '\'')把字符串中所有u'替换为'，消除Python unicode标识，转为Snowflake可解析的标准JSON结构。
• 转换为VARIANT类型：PARSE_JSON()函数将处理后的字符串转为Snowflake的变体类型，支持后续的JSON层级操作。
• 扁平化数组：LATERAL FLATTEN()展开数组中的每个字典元素，让数组内的每个对象单独成为一行。
• 提取目标字段：通过flattened.value:macAddress::VARCHAR直接从字典中取出macAddress的值，并转为VARCHAR类型。

示例验证 #

若你的ports字段值为[{u'position': 1, u'macAddress': u'00:8C:FA:C1:7C:88'}, {u'position':2, u'macAddress': u'00:8C:FA:5E:98:81'}]，执行查询后会输出：

00:8C:FA:C1:7C:88
00:8C:FA:5E:98:81

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