CODEWITHSUNDEEP
sqlpresto
Python Learner
Python Learner

Reputation: 185

Presto - Turn 2 arrays into separate mapped entities using each index of the array

I cannot, for the life of me, figure out how to do this in presto. After querying I have two aggregated arrays a and b. I'd like to figure out how to turn each index of the array into a map with static labels for each value in a and b.

See below for example. I've toyed around all the various array map functions in the Presto documentation but cannot seem to figure it out. Any thoughts?

Note: The length of a and b will always be the same as each other, but can vary between lengths of 1 through 10

Input

select
array_agg('a','b','c') as a,
array_agg(1,2,3) as b
from etc...

which turns into

a b
['a','b','c'] [1,2,3]

Then, after some transformation...

Desired Output:

{
    'static_label_char' -> 'a',
    'static_label_num' -> 1
},
{
    'static_label_char' -> 'b',
    'static_label_num' -> 2
},
{
    'static_label_char' -> 'c',
    'static_label_num' -> 3
}

Upvotes: 0

Views: 1826

Answers (1)

Guru Stron
Guru Stron

Reputation: 143098

Not sure that you can create a map containing different types as values, but you can zip your arrays and transform the result to rows with named fields:

SELECT transform(
  zip(a, b),
  r -> cast(r AS ROW(static_label_char CHAR, static_label_num SMALLINT)))
FROM 
    (SELECT ARRAY['a','b','c'] AS a, ARRAY[1,2,3] AS b)

Output:

_col0
[{static_label_char=a, static_label_num=1}, {static_label_char=b, static_label_num=2}, {static_label_char=c, static_label_num=3}]

And then you can use dot notation to access the named field. For example with unnest:

SELECT r.static_label_char, r.static_label_num
FROM 
    (SELECT transform( zip(a, b),
         r -> cast(r AS ROW(static_label_char CHAR, static_label_num SMALLINT))) AS rows
    FROM 
        (SELECT ARRAY['a','b','c'] AS a, ARRAY[1,2,3] AS b) )
CROSS JOIN UNNEST(rows) AS t (r);

Output:

static_label_char static_label_num
a 1
b 2
c 3

Upvotes: 1

Related Questions