Snowflake using flatten for array flattening returning unknown function-CodePudding

I have a function that returns an an array of timestamps. I want to convert these timestamps to dates in yyyy-mm-dd format. I want to flatten the array column that is returned but I am getting:

SQL compilation error: Unknown function FLATTEN

when I run:

with cte as (SELECT 
    array_construct('2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date) AS array
    ,'2021-09-11'::date AS max_date
    ,date_filter
        (
          '2021-09-12'::date,
          array_construct('2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date)
          
        ) as dates )
        
        select flatten(cte.dates) from cte

Function is:

CREATE OR REPLACE FUNCTION date_filter (max_date DATE, date_list ARRAY)
  RETURNS ARRAY
  LANGUAGE JAVASCRIPT
  AS $$
  var dates = DATE_LIST;
  return  dates.filter(date => date < MAX_DATE);
  $$
  ;

CodePudding user response：

flatten is a table function, not a scalar function. It's being called in the SQL as a scalar function rather than a table function, and that's why Snowflake is reporting it can't find the function.

If you change this line to use it as a table function, it seems to do what you want:

select * from cte, table (flatten(cte.dates))