Flatten Nested Array and Aggregate in Snowflake

Question

My table column has nested arrays in a Snowflake database. I want to perform some aggregations using SQL (Snowflake SQL).

My table name is: DATA

The PROJ column is of VARIANT data type. The nested arrays will not always be 3, and I demonstrated that in the DATA table.


| ID |             PROJ              | LOCATION |
|----|-------------------------------|----------|
| 1  |[[0, 4], [1, 30], [10, 20]]    |    S     |
| 2  |[[0, 2], [1, 20]]              |    S     |
| 3  |[[0, 8], [1, 10], [10, 100]]   |    S     |

Desired Output:

| Index | LOCATION |  Min | Max | Mean|
|-------|----------|------|-----|-----|
| 0     |    S     |  2   |  8  | 4.66|
| 1     |    S     |  10  |  30 | 20  |
| 10    |    S     |  20  |  100| 60  |

Lukasz Szozda · Accepted Answer · 2022-07-05 16:14:57Z

3

First the nested array should be flattened, then Index is the first element of subarray and Value is the second element(array is 0-based):

CREATE OR REPLACE TABLE DATA
AS
SELECT 1 AS ID, [[0, 4], [1, 30], [10, 20]]   AS PROJ UNION
SELECT 2 AS ID, [[0, 2], [1, 20]]             AS PROJ UNION
SELECT 3 AS ID, [[0, 8], [1, 10], [10, 100]]  AS PROJ;

Query:

SELECT s.VALUE[0]::INT AS Index,
       MIN(s.VALUE[1]::INT) AS MinValue,
       MAX(s.VALUE[1]::INT) AS MaxValue,
       AVG(s.VALUE[1]::INT) AS MeanValue
FROM DATA
,LATERAL FLATTEN(input=> PROJ) s
GROUP BY s.VALUE[0]::INT
ORDER BY Index;

Output:

answered Jul 5, 2022 at 16:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Diego-S Over a year ago

Thanks Lukasz! @LukaszSzozda. What if I have another column I would like to keep in my output? Edited the question so you could see what I mean.

Lukasz Szozda Over a year ago

@Diego-S Please do not edit question that invalidates existing answers. It should be another question. Plus it is not defined how to choose the row in case if location is different between rows. - sample: i.sstatic.net/GRHnh.png

Diego-S Over a year ago

Sorry bout that. The location is the same for all, would that i.stack.imgur.com/GRHnh.png still work?

Lukasz Szozda Over a year ago

@Diego-S If they are the same than wrapping with agg function will work: ,MIN(location) AS location

Collectives™ on Stack Overflow

Flatten Nested Array and Aggregate in Snowflake

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related