如何在Presto中交叉联接取消嵌套JSON数组

Question

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

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

如何编写 Presto 查询以提供所有条目的平均b值？

到目前为止，我认为我需要使用类似 Hive 的侧视图爆炸的东西，其等价物是 Presto 中的交叉连接 unnest。

但是我坚持如何为cross join unnest编写Presto查询。

如何使用cross join unnest展开所有数组元素并选择它们？

Answer 1

下面是一个示例

with example(message) as (
VALUES
(json '{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}'),
(json '{"payload":[{"type":"c","value":"7"}, {"type":"b","value":"3"}]}')
)

SELECT
        n.type,
        avg(n.value)
FROM example
CROSS JOIN
    UNNEST(
            CAST(
                JSON_EXTRACT(message,'$.payload')
                    as ARRAY(ROW(type VARCHAR, value INTEGER))
                    )
                ) as x(n)
WHERE n.type = 'b'
GROUP BY n.type

with定义了一个名为 example 的公用表表达式 （CTE），其列别名为 message

VALUES返回逐字表行集

UNNEST 在单行的列中获取数组，并将数组的元素作为多行返回。

CAST 正在将JSON类型更改为UNNEST所需的ARRAY类型。这很容易成为ARRAY<MAP<但我发现ARRAY(ROW(更好，因为您可以指定列名，并在选择子句中使用点表示法。

JSON_EXTRACT使用 jsonPath 表达式返回payload键的数组值

avg()和group by应该是熟悉的 SQL。

Answer 2

正如你所指出的，这最终在Presto 0.79中实现。 :)

下面是此处强制转换的语法示例：

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

特别的建议是，Presto中没有像Hive那样的"字符串"类型。这意味着如果您的数组包含字符串，请确保使用类型"varchar"，否则您会收到错误消息，指出"类型数组不存在"

，这可能会产生误导。

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

Answer 3

问题是我运行的是旧版本的Presto。

unnest 在 0.79 版中添加

https://github.com/facebook/presto/blob/50081273a9e8c4d7b9d851425211c71bfaf8a34e/presto-docs/src/main/sphinx/release/release-0.79.rst