如何在Presto中交叉连接一个JSON数组 [英] How to cross join unnest a JSON array in Presto

问题描述

给定一个包含如下JSON列的表：

b，value：9}，{type：a，value：8}]}
{payload：[{type：c ，value：7}，{type：b，value：3}]}

如何编写一个Presto查询，以便为所有条目提供平均值 b 值？

到目前为止，我认为我需要使用像Hive的横向视图爆炸，其等效为

但是我一直在写如何为<$ c $编写Presto查询c>交叉连接unnest 。

如何使用交叉连接unnest 来展开所有数组元素并选择它们？

解决方案

正如你指出的那样，这最终在Presto 0.79中实现。 ：）

下面是一个来自这里：

  select cast（cast（'[1,2,3]'as json）作为数组< bigint>）; 
  

特别的建议是，Presto中没有像'Hive'那样的'string'类型。
这意味着如果你的数组包含字符串，请确保使用'varchar'类型，否则你会得到一个错误信息，说'类型数组不存在'，这可能会引起误解。

 选择cast（cast（'[1，2，3]'as json）作为数组< varchar>）; 
  

Given a table that contains a column of JSON like this:

{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}
{"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}

How can I write a Presto query to give me the average b value across all entries?

So far I think I need to use something like Hive's lateral view explode, whose equivalent is cross join unnest in Presto.

But I'm stuck on how to write the Presto query for cross join unnest.

How can I use cross join unnest to expand all array elements and select them?

解决方案

As you pointed out, this was finally implemented in Presto 0.79. :)

Here is an example of the syntax for the cast from here:

select cast(cast ('[1,2,3]' as json) as array<bigint>);

Special word of advice, there is no 'string' type in Presto like there is in Hive. That means if your array contains strings make sure you use type 'varchar' otherwise you get an error msg saying 'type array does not exist' which can be misleading.

select cast(cast ('["1","2","3"]' as json) as array<varchar>);

这篇关于如何在Presto中交叉连接一个JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！