presto-unnest jsonMap

wswtfjt7  于 2021-07-13  发布在  Hadoop
关注(0)|答案(1)|浏览(349)

以下文章:如何交叉连接presto和presto unnest json中的unnest json数组我无法将我的jsonMap到所需的表中。
当我运行这个presto时:

SELECT
    *
FROM UNNEST(
        CAST(
            JSON_EXTRACT('{"1":{"a":10,"b":11},"2":{"a":20,"b":21}}', '$.1') AS ARRAY(
                VARCHAR
                -- ROW(VARCHAR, BIGINT)
                -- MAP(VARCHAR, MAP(VARCHAR, BIGINT))
            )
        )
    ) AS x(n)

我得到:

(INVALID_CAST_ARGUMENT) Cannot cast to array(varchar). Expected a json array, but got {
{"a":10,"b":11} ...

但我的理想答案是:

a | b
 ---+---
 10 | 11
 20 | 21

我该怎么做 UNNESTMap 而不是数组?

brgchamk

brgchamk1#

正确的json路径是 $.* 但是普雷斯托不支持通配符,因此您可以将json转换为map of maps并使用它:

SELECT maps['a'] a, maps['b'] b
FROM (UNNEST(map_values(
        CAST(JSON '{"1":{"a":10,"b":11},"2":{"a":20,"b":21}}' AS MAP(VARCHAR , MAP(VARCHAR, INTEGER)))))) as tmp(maps);

相关问题