尝试使用 json 从列中提取文本值varchar
但在模式下运行时在雪花上收到无效的参数错误。这个 json 的结构与我习惯看到的结构略有不同。
尝试过这些来提取文本:
changes:comment:new_value::varchar
changes:new_value::varchar
changes:comment::varchar
JSON 如下所示:
{
"comment":
{
"new_value": "Hello there. Welcome to our facility.",
"old_value": ""
}
}
希望提取此列中的数据,以便输出为:
Hello there. Welcome to our facility.
您无法从VARCHAR
中提取字段。如果你的字符串是 JSON,你必须将其转换为 VARIANT
类型,例如通过 PARSE_JSON
函数。
示例如下:
create or replace table x(v varchar) as select * from values('{
"comment":
{
"new_value": "Hello there. Welcome to our facility.",
"old_value": ""
}
}');
select v, parse_json(v):comment.new_value::varchar from x;
--------------------------------------------------------------+------------------------------------------+
V | PARSE_JSON(V):COMMENT.NEW_VALUE::VARCHAR |
--------------------------------------------------------------+------------------------------------------+
{ | Hello there. Welcome to our facility. |
"comment": | |
{ | |
"new_value": "Hello there. Welcome to our facility.", | |
"old_value": "" | |
} | |
} | |
--------------------------------------------------------------+------------------------------------------+