if we have two arrays with same shape. How do we create/explode the separate rows for each element in array associated with other element in second array in Presto SQL.
For eg.,
| Array1 | Array2 |
| ------------------------ | ------------------------- |
| [0, 1, 34, 55, 68, 100] | [40, 10, 30, 50, 60, 88] |
Desired result
| Column_a | Column_b |
| -------- | -------- |
| 0 | 40 |
| 1 | 10 |
| 34 | 30 |
| 55 | 50 |
| 68 | 60 |
| 100 | 88 |
I tried using
SELECT Column_a, Column_b FROM a
CROSS JOIN UNNEST(Array1) AS t (Column_a)
CROSS JOIN UNNEST(Array2) AS t (Column_b)
But it gives output with every other array element.
>Solution :
UNNEST allows flattening several arrays at a time:
SELECT Column_a, Column_b
FROM a
CROSS JOIN UNNEST(Array1, Array2) AS t (Column_a, Column_b)
If length of the arrays differ then the shorter one will be padded with null‘s