如何在Snowflake中从JSON Variant提取值并加载至其他表

你的问题出在每个字段（比如Appointment Date）的值是转义后的JSON字符串，并非原生的JSON对象，直接用.value无法识别，所以返回null。需要先把这个字符串解析为JSON对象，再提取对应值。

1. 直接查询提取值 #

使用PARSE_JSON()函数将字符串转为Variant类型，再提取value字段：

SELECT
  PARSE_JSON(JSON_DATA:"Appointment Date"):value AS "Appointment Date",
  PARSE_JSON(JSON_DATA:"Appointment ID"):value AS "Appointment ID",
  PARSE_JSON(JSON_DATA:"Appointment Remark"):value AS "Appointment Remark"
FROM xyz.abc.CAN_IB_SCHEDULING_test;

该查询会返回你期望的结果：

2. 将提取后的数据加载到新表 #

如果要把提取结果存入另一张表，可使用CREATE TABLE AS语句：

CREATE OR REPLACE TABLE xyz.abc.CAN_IB_SCHEDULING_CLEANED AS
SELECT
  PARSE_JSON(JSON_DATA:"Appointment Date"):value::DATE AS "Appointment Date",
  PARSE_JSON(JSON_DATA:"Appointment ID"):value::STRING AS "Appointment ID",
  PARSE_JSON(JSON_DATA:"Appointment Remark"):value::STRING AS "Appointment Remark",
  PARSE_JSON(JSON_DATA:"Appointment Insert Date"):value::TIMESTAMP AS "Appointment Insert Date"
FROM xyz.abc.CAN_IB_SCHEDULING_test;

这里可以根据实际需求给字段指定对应的类型（比如DATE、TIMESTAMP），确保数据类型准确。

额外说明 #

如果后续要避免这种嵌套字符串的问题，可以检查S3中的原始JSON数据格式，确保字段值是原生JSON对象而非转义后的字符串；或者在COPY INTO时，通过文件格式参数调整解析逻辑，但当前场景下用PARSE_JSON()是最直接的修复方式。

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