I have a table of data with multiple arrays. I need to expand the arrays into clean rows and columns so I can better query, join, group, etc
Table:
| ID | Array A | Array B | Array C |
|---|---|---|---|
| 1 | {A1, A2, A3} | {B1, B2, B3} | {C1, C2, C3} |
| 2 | {A4, A5} | {B4, B5} | {C4, C5} |
I need to get the data broken into rows/columns in this format:
| ID | Col A | Col B | Col C |
|---|---|---|---|
| 1 | A1 | B1 | C1 |
| 1 | A2 | B2 | C2 |
| 1 | A3 | B3 | C3 |
| 2 | A4 | B4 | C4 |
| 2 | A5 | B5 | C5 |
The arrays can be different lengths per row but within the row will always be the same size. As noted above, ID 1 has 3 arrays of 3 elements, ID 2 has 3 arrays of 2 elements. These arrays can have up to 16 elements in them.
I tried to play around with ArrayJoin and unnest but couldn’t get it to group the data correctly.
New contributor
FVZ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.