flatten snowflake arrays into rows

Question

I have a snowflake table with 3 arrays (contained in one table row):

order array:

part array

price array:

How can i convert the arrays into rows, so that each row is composed of 3 columns, so that the first row is comprised of 3 columns, made up of the first cell from each array, and second row is comprised of the 3 columns - from the second cell from each array , etc..... so that the conversion result will be 7 table rows (matching the 7 cells in each array).

the flatten keyword to break array cells into rows, eludes me.

user_5070879 · Answer

Using tally table to access array elements:

Data:

CREATE OR REPLACE TABLE t
AS
SELECT PARSE_JSON('[1466369, 1466369, 1466369, 1466369, 1466369, 1466369, 1466369]') AS orders,
PARSE_JSON ('[17083, 40052, 173810, 71874, 104769, 121607,  47652]') AS part,
PARSE_JSON ('[9000.72, 9920.5, 11302.86, 18458.7, 26606.4,  27686.2, 36791.95]') AS price;

Query:

SELECT  orders[s.t]::INT AS orders, part[s.t]::INT AS part, price[s.t] AS price
FROM t
LEFT JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS s(t)
  ON s.t < GREATEST(ARRAY_SIZE(t.orders), ARRAY_SIZE(t.part), ARRAY_SIZE(t.price));

Output:

Alternatively:

SELECT  s1.index, s1.value, s2.value, s3.value
FROM t
,TABLE(FLATTEN(t.orders)) s1
,TABLE(FLATTEN(t.part)) s2
,TABLE(FLATTEN(t.price)) s3
WHERE s1.INDEX = s2.INDEX
  AND s2.INDEX = s3.INDEX;

Output:

Though this approach will explode really fast size_array_1 * size_array_2 * size_array_3.

EDIT:

I tried placing a value of null (undefined) in one of the arrays values, and when i do - the query would not return the row with null as one of the column values (returned 6 rows instead of 7) . is there away to address this

CREATE OR REPLACE TABLE t
AS
SELECT PARSE_JSON('[1466369, null, 1466369, 1466369, 1466369, 1466369, 1466369]') AS orders,
PARSE_JSON ('[17083, 40052, 173810, 71874, 104769, 121607,  47652]') AS part,
PARSE_JSON ('[9000.72, 9920.5, 11302.86, 18458.7, 26606.4,  27686.2,36791.95]') AS price;

Query:

SELECT  s1.index, s1.value, s2.value, s3.value
FROM t
,TABLE(FLATTEN(t.orders, OUTER=> TRUE)) s1
,TABLE(FLATTEN(t.part, OUTER=> TRUE)) s2
,TABLE(FLATTEN(t.price, OUTER=> TRUE)) s3
WHERE s1.INDEX = s2.INDEX
  AND s2.INDEX = s3.INDEX;

Output:

flatten snowflake arrays into rows

1 Answers