What is the equivalent of postgresSQL unnest() in snowflake sql

Question

How do i modify the PostgresSQL in snowflake?

UNNEST(array[
    'x' || to_char(date_trunc('MONTH', max(date)), 'Mon YYYY' ,
    'y' || to_char(date_trunc('MONTH', max(date)), 'Mon YYYY')
    )])

Answer 1

You can use "flatten" to break out values from the array, and then "table" to convert the values into a table:

-- Use an array for testing:
select array_construct(1, 2, 3, 4, 5);

-- Flattens into a table with metadata for each row:
select * from table(flatten(input => array_construct(1, 2, 3, 4, 5)));

--Pulls out just the values from the array:
select value::integer from table(flatten(input => array_construct(1, 2, 3, 4, 5)));

The "::integer" part casts the values to the data type you want from the array. It's optional but recommended.

You can approximate the syntax of unnest by creating a user defined table function:

create or replace function UNNEST(V array)
returns table ("VALUE" variant)
language SQL
aS
$$
    select VALUE from table(flatten(input => V))
$$;

You would call it like this:

select * from table(unnest(array_construct(1, 2, 3, 4, 5)));

This returns a table with a single column named VALUE of type variant. You can make a version that returns strings, integers, etc.