Presto: cast array into map

Question

I have a table like

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   array>                    
two_key_value   array>

and want to convert it to

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   map>                       
two_key_value   map>

using presto. There is lateral view inline but it doesn't really work in presto. How can I do this?

user_65458 · Answer

Based on the provided information, you basically need two things:

Upgrade to something more recent -- latest from Maven Central / Github, or a Starburst-supported (and free) distribution from https://www.starburstdata.com/starburst-presto-sql/ (disclaimer: I am from Starburst). 0.175 is really a bit out of date.
Use map_from_entries(one_key_value) (docs: https://trino.io/docs/current/functions/map.html#map_from_entries)

user_1700467 · Answer

I have not tested thought, but below expression should help:

map( transform(one_key_value, e -> e.key), transform(one_key_value, e -> e.value))
map( transform(two_key_value, e -> e.key), transform(two_key_value, e -> e.value))

AS per Presto 0.175 docs:

map(array, array) → map Returns a map created using the given key/value arrays.

SELECT map(ARRAY[1,3], ARRAY[2,4]); -- {1 -> 2, 3 -> 4}

We can use array transform function to build the array of keys and values from input field ( array>)

transform(array, function) → ARRAY Returns an array that applies function to each element of array

Presto: cast array<struct<key:string,value:array<string>>> into map<string,array<string>>

2 Answers