从半结构化数据中检索雪花值

Question

我试图从变体列中的Extern外表X中检索雪花半结构化数据的健康值。

该代码的一个示例如下：

[
  {
    "party":
 "[{\"class\":\"Farmer\",\"gender\":\"Female\",\"ethnicity\":\"NativeAmerican\",\"health\":2},
{\"class\":\"Adventurer\",\"gender\":\"Male\",\"ethnicity\":\"White\",\"health\":3},
{\"class\":\"Farmer\",\"gender\":\"Male\",\"ethnicity\":\"White\",\"health\":0},
{\"class\":\"Banker\",\"gender\":\"Female\",\"ethnicity\":\"White\",\"health\":0}
  }
] 

我试过从https://community.snowflake.com/s/article/querying-semi-structured-data上阅读雪花文档

我还尝试了以下查询来简化查询：

SELECT result.value:health AS PartyHealth 
FROM X 
WHERE value = 'Trail'
AND name = 'Completed' 
AND PartyHealth > 0, 
TABLE(FLATTEN(X, 'party')) result

和

SELECT [0]['party'][0]['health'] AS Health
FROM X 
WHERE value = 'Trail'
AND name = 'Completed' 
AND PH > 0;

我试图从表X中从包含变量方的列extra中检索健康值，其中包含4个重复值0-3。我不知道怎么做，有人能告诉我如何在雪花中查询半结构化数据，考虑到文档没有多大意义吗？

Answer 1

首先，您发布的JSON值似乎格式化错误(可能是复制粘贴问题)。

下面是一个有用的例子：

• 首先，您的JSON格式化如下： [{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]
• 创建要测试的表： CREATE OR REPLACE TABLE myvariant (v variant);
• 将JSON值插入到此表中： INSERT INTO myvariant SELECT PARSE_JSON('[{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]');
• 现在，要选择一个从列名开始的值，在我的例子中是v，并且由于您的JSON是一个数组，所以我指定了第一个值，然后在那里展开，如下所示： SELECT v[0]:party[0].health FROM myvariant;

上面给我的是：

​

​

对于其他行，您可以简单地这样做：

SELECT v[0]:party[1].health FROM myvariant;
SELECT v[0]:party[2].health FROM myvariant;
SELECT v[0]:party[3].health FROM myvariant;

Answer 2

另一个选择可能是使数据更像一个表..。我发现使用JSON比JSON更容易:-)

代码在底部-只要复制/粘贴，它运行在雪花返回截图下面。

关键的多佐是侧方扁平

​

​

 SELECT  d4.path, d4.value 
 from  
 lateral flatten(input=>PARSE_JSON('[{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]') ) as d  ,  
 lateral flatten(input=> value) as d2 ,  
 lateral flatten(input=> d2.value) as d3 ,  
 lateral flatten(input=> d3.value) as d4