How can I convert the array of integers to rows?

Question

I have a table that has:

id, timestamp, [array of integers]

How can I convert the array of integers to rows? Almost the opposite of array_agg.

eg,

1, ts, [1,2,3]
2, ts, [7,8,9]

would be

1, ts, 1
1, ts, 2
1, ts, 3
2, ts, 7
2, ts, 8
2, ts, 9

I've read through https://docs.snowflake.net/manuals/sql-reference/udf-js-table-functions.html but it's not clear that this will work. I am trying to avoid using a scripting language outside of the database. Thanks!

Answer 1

Use FLATTEN. It has various options, including things like the value of the field, but also index in the array etc.

A full example below:

create or replace table x(i int, s string, v variant);
insert into x 
select column1, column2, parse_json(column3) from values
  (1, 'ts1', '[1,2,3]'),
  (2,'ts2','[7,8,9]');

select * from x;
---+-----+------+
 I |  S  |  V   |
---+-----+------+
 1 | ts1 | [    |
   |     |   1, |
   |     |   2, |
   |     |   3  |
   |     | ]    |
 2 | ts2 | [    |
   |     |   7, |
   |     |   8, |
   |     |   9  |
   |     | ]    |
---+-----+------+

select i, s, f.value as newcolumn from x, table(flatten(x.v)) f;
---+-----+-----------+
 I |  S  | NEWCOLUMN |
---+-----+-----------+
 1 | ts1 | 1         |
 1 | ts1 | 2         |
 1 | ts1 | 3         |
 2 | ts2 | 7         |
 2 | ts2 | 8         |
 2 | ts2 | 9         |
---+-----+-----------+