I currently have a table with a column C where entry for some row R in column C contains an array of structs S = {a: 1, b:2, c: 3}. I want to combine all the "a" values in this table and retain all the distinct "a" values.
That is to say, for R: 1 and C: 1, I have some array A = [S(a: 1, b: 2, c: 3), S(a: 3, b: 4, c: 5)] --> new table that looks like
| a |
|---|
| 1 |
| 3 |
How would I do this? Thank you.
Edit: Adding a sample of what the original table looks like:
| Row | Data |
|---|---|
| 1 | Array<Struct(a: 1, b: 2, c: 3), Struct(a: 3, b: 4, c: 5)> |
| 2 | Array<Struct(a: 5, b: 3, c: 3), Struct(a: 6, b: 8, c: 9)> |
Desired Output:
| a |
|---|
| 1 |
| 3 |
| 5 |
| 6 |
CodePudding user response：
Consider below simple approach
select record.a
from your_table, unnest(data) record
if applied to sample data in your question - output is
