Do memberwise operations on arrays in Snowflake-CodePudding

If I have a variant column in Snowflake that contains an ARRAY, I can do operations on each element by using flatten to convert it to a table:

SELECT value FROM LATERAL FLATTEN(arr)

This seems somewhat clunky to me. Is there a less verbose way of doing this?

CodePudding user response：

Operations is a broad term, but yes, it's possible to do many operations on arrays without flattening them.

The first way is to use one of the built-in array functions. They all start with array_ and a list is available here: https://docs.snowflake.com/en/sql-reference/functions-all.html

Another option to process without flattening is using a procedural UDF in JavaScript, Python, Java, or Scala. Here's a hello-world level example:

create or replace function SUM_ARRAY(ARR array)
returns float
language javascript
strict immutable
as
$$
    let sum = 0;
    for (let i=0; i<ARR.length; i  ) {
        sum  = ARR[i];
    }
    return sum;
$$;

with DATA as
(
    select [1,2,3] as ARR
)
select sum_array(ARR) ARRAY_SUM from DATA
;