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.

Mehmet Güngören · Accepted Answer · 2023-03-24 18:53:03Z

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

answered Mar 24, 2023 at 18:53

Mehmet Güngören

2,8342 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unnest two columns with Redshift Spectrum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related