Presto unnest json map

Question

Following these posts: How to cross join unnest a JSON array in Presto and Presto unnest json I am unable to map my JSON into desired table.

When I run this 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)

I get :

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

But my ideal intended answer is:

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

How do I UNNEST a Map and not an array?

Answer 1

Correct json path for your goal would be $.* but wildcards are not supported by presto, so you can cast your json to map of maps and work with that:

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);