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?

score 2 · Accepted Answer · answered Apr 16 '21 at 15:59

2

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

answered Apr 16 '21 at 15:59

Guru Stron

102,774
10
95
132

1

Thanks a lot. I didn't know about `MAP_VALUES` keyword. – vishwarajanand Apr 17 '21 at 10:35
2

@vishwarajanand was glad to help! There are a lot of useful functions, check out the [docs](https://prestodb.io/docs/current/functions.html). – Guru Stron Apr 17 '21 at 20:59

Presto unnest json map

1 Answers1

Linked