在雪花中查询变体数据

Question

这是我在示例中使用的数据变量源表。我想进行查询以将这些数据从变体 src 解析到雪花中的表中。

{
"col1": bool,
"col2": null,
"col3": "datetime",
"col4": int,
"col5": "string",
"col6": "string",
"array": [
{
"x": bool,
"y": null,
"v": "datetime",
"z": int,
"w": "string",
"q": "string",
"obj": {
"a": "bool",
"b": "float"
},
"col7": "datetime"
}
]
}

-- 这是我试过的

SELECT 
src:col1::string as col1,
src:col2::string as col2,
src:col3::string as col3,
src:col4::string as col4,
src:col5::string as col5,
src:col6::string as col6,
s.value:x::string as S_x,
s.value:y::string as s_y,
s.value:v::string as s_v,
s.value:z::string as s_z,
s.value:w::string as s_w,
s.value:q::string as s_q,
s.value:obj.value:a::string as s_obj_a,
s.value:obj.value:b::string as s_obj_b,
src:col7::string as col7 
FROM tblvariant
, table(flatten(src:s)) s
;

除了这两列(a，b(为空，而它们应包含其数据外，一切都在工作。 有什么建议吗？ 非常感谢！

Answer 1

您的示例 JSON 与您的 SQL 不匹配。"阶段"和"元数据"在哪里？无论如何，问题似乎与额外的"价值"关键字有关。

create or replace table tblvariant ( src variant )
as select parse_json (' 
{
"col1": "bool",
"col2": null,
"col3": "datetime",
"col4": "int",
"col5": "string",
"col6": "string",
"stages": [
{
"x": "bool",
"y": null,
"v": "datetime",
"z": "int",
"w": "string",
"q": "string",
"obj": {
"a": "bool",
"b": "float"
},
"col7": "datetime"
}
]
}' );

如您所见，我修改了您的示例 JSON 并将"数组"重命名为"阶段"(根据您的 SQL(。此 SQL 检索 a 和 b 的值：

SELECT 
src:col1::string as col1,
src:col2::string as col2,
src:col3::string as col3,
src:col4::string as col4,
src:col5::string as col5,
src:col6::string as col6,
s.value:x::string as S_x,
s.value:y::string as s_y,
s.value:v::string as s_v,
s.value:z::string as s_z,
s.value:w::string as s_w,
s.value:q::string as s_q,
s.value:obj.a::string as s_obj_a,
s.value:obj.b::string as s_obj_b,
src:col7::string as col7 
FROM tblvariant
, table(flatten(src:stages)) s
-- , table(flatten(s.value:metadata)) m
;

Answer 2

s.value：obj .value：a ：：string as s_obj_a，

s.value：obj.value：b ：：stringas s_obj_b，

访问对象的键可以使用点(.(表示法来完成。您无需使用GET_PATH(:( 运算符即可访问这些字段：

s.value:metadata.a::string as s_m_a,
s.value:metadata.b::string as s_m_b,

您也不需要对stages数组中的metadata对象运行第二个FLATTEN，除非您确实需要每个metadata键一个独占行，假设metadata是对象类型而不是嵌套数组。如果您只想将值提取到与每个数组行相同的级别，只需使用上述值就足够了。