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

Question

I have a table like

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   array<struct<key:string,value:array<string>>                    
two_key_value   array<struct<key:string,value:array<string>>

and want to convert it to

name            string                                      
address         string                                      
timezone        string                                      
one_key_value   map<string,array<string>>                       
two_key_value   map<string,array<string>>

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

Answer 1

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<struct<key:string,value:array<string>>)

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

Answer 2

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

1. 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.
2. Use map_from_entries(one_key_value) (docs: https://trino.io/docs/current/functions/map.html#map_from_entries)