Unnest two columns with Redshift Spectrum

Question

I have this table called results, with two nested arrays in columns students and grades:

class	students	grades
C1	[S1, S2, S3]	[C, A, B]
C2	[S3, S4]	[A, B]

I'd like to unnest it to the following:

class	student	grade
C1	S1	C
C1	S2	A
C1	S3	B
C2	S3	A
C2	S4	B

I tried the following request in Redshift, but it creates duplicates and the results is not correct:

SELECT class, student as s, grade as g
FROM spectrum.results r
LEFT JOIN r.students s ON true
LEFT JOIN r.grades g ON true;

Any idea is appreciated.

Answer 1

You need UNNEST both column together.

WITH dataset (class, students, grades) AS (
    VALUES ('C1', array['S1', 'S2', 'S3'], array['C', 'A', 'B']),
     ('C2', array['S3', 'S4'], array['A', 'B'])
) 
select class,
   T.students,
   T.grades
from dataset
CROSS JOIN UNNEST (students, grades) T (students, grades)
ORDER BY class, students, grades

OUTPUT

class	student	grade
C1	S1	C
C1	S2	A
C1	S3	B
C2	S3	A
C2	S4	B