Presto unnest json

SELECT JSON_EXTRACT('{"payload":[{"type":"b","value":"9"}, {"type":"a","value":"8"}]}','$.payload')

gives:

[{"type":"b","value":"9"}, {"type":"a","value":"8"}]

which is ARRAY<MAP<VARCHAR,VARCHAR>>.you can change your query to: SELECT x.n FROM UNNEST (CAST(JSON_EXTRACT('{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}','$.payload') AS ARRAY<MAP<VARCHAR, VARCHAR>>)) AS x(n)

One possible interpretation of the return datatype is the following:

ARRAY<MAP<VARCHAR,VARCHAR>>

but has the downside that accessing values in a map can't be done using dot notation.

An alternative datatype to assume would be this:

ARRAY(ROW(type VARCHAR, value VARCHAR))

Which resembles the ARRAY<STRUCT< Hive datatype equivalent.

Massive digression here>> JSON is a bit ambiguous.

Which one is correct? Is a JSON object a representation of a map ( hashmap, dictionary, key-value pairs whatever your language calls it) or is it more like a struct (object, class, bag of names properties whatever your language calls it)? It originates from JavaScript ( Object Notation) intended to cater for arrays, objects and primitive types, but more widespread usage means it has ambiguous mapping (ha) in other languages. Perhaps functionally equivalent but in theory the MAP should be quicker for random reads/writes and the ROW probably has some extra object oriented overhead, but this is all implemented in Java where everything is an object anyway so I have no answer. Use whatever you like. << I digress.

You found this a bit verbose:

SELECT x.n['type'] as "type",x.n['value'] as "value"FROM UNNEST (            CAST(                JSON_EXTRACT('{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}'                             ,'$.payload')                 AS ARRAY<MAP<VARCHAR, VARCHAR>>                )            )         AS x(n)

Here's the alternative

SELECT    n.type,    n.valueFROM UNNEST(            CAST(                JSON_EXTRACT(                            '{"payload":[{"type":"b","value":"9"},{"type":"a","value":"8"}]}'                            ,'$.payload'                            )                 as ARRAY(ROW(type VARCHAR, value VARCHAR))                )            ) as x(n)

It's just as verbose; the names of the columns are just shifted to the CAST expression, but perhaps (subjective!) easier to look at.

You can use JSON_EXTRACT,CAST and finally UNNEST to respective columns

SELECT type,value FROM UNNEST(CAST(JSON_EXTRACT('{"payload":[{"type":"b","value":"9"},                           {"type":"a","value":"8"}]}'                         ,'$.payload'                 ) as ARRAY(ROW(type VARCHAR, value VARCHAR)            )       )) as x(type,value)

gives output as below

 type | value------+------- b    | 9 a    | 8