I'm trying to flatten arrays in different columns with different lengths without duplicating the results.
For example (using standard SQL):
WITH
x AS (
SELECT
ARRAY[1,
2,
3] AS a,
ARRAY[1,
2] AS b)
SELECT
a,
b
FROM
x,
x.a,
x.b
Produces:
+-----++-----+
| a | b |
+-----++-----+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
+-----++-----+
It should look like this:
+-----++-----+
| a | b |
+-----++-----+
| 1 | 1 |
| 2 | 2 |
| 3 | null |
+-----++-----+
See Question&Answers more detail:os