How to get maximum value from an array column in Snowflake?

user_10966532 0
Viewed 2084

I have a column in snowflake table which is varchar type and contains comma(,) seperated values in it. I used split(mycolumn,',') to change all the comma(,) seperated values into an array. Now, After using split function i have an array of values.

How can i get maximum value from this array?

I Tried both max() and greatest() functions but no use.

Thanks in Advance.

snowflake-schema snowflake-task snowflake-cloud-data-platform data-warehouse database
2 Answers

Something like this:

    select sum(c.value::number) as val
from dual,
     lateral flatten(input=>split('1,2,33',',')) c;

(generally you need to flatten the array before you can SUM it)

user_122009880

You can accomplish this by using LATERAL FLATTEN

    CREATE OR REPLACE TEMPORARY TABLE TestTable (
  ID  INTEGER
 ,ColLabel  VARCHAR
 ,ColValues  VARIANT
)
AS
  SELECT $1
        ,$2
        ,PARSE_JSON($3)
    FROM VALUES
           (1, 'Jon', '[2,4,6]')
          ,(2, 'Susan', '[10]')
          ,(3, 'Sally', '[25,25,25,25]')
;
SELECT T.ID
      ,T.ColLabel
      ,SUM(F.VALUE::INTEGER) AS ColValues_Elem
  FROM TestTable AS T
      ,LATERAL FLATTEN(T.ColValues) AS F
      GROUP BY T.ID, T.ColLabel
;

Results:

ID  COLLABEL    COLVALUES_ELEM
1   Jon         12
2   Susan       10
3   Sally       100
user_18549350
Related