CODEWITHSUNDEEP
arraysansi-sqlsnowflake-cloud-data-platform
Curtis Blanchette
Curtis Blanchette

Reputation: 23

How to FLATTEN a set of ARRAY_AGGS in Snowflake SELECT

I'm trying to make a SELECT on multiple joined VARIANT columned tables. The main record is returned as DATA and all supporting information around it are made up of supporting joined tables returned as INCLUDED.

I'm using an ARRAY_CONSTRUCT_COMPACT on the supporting VARIANT records AND ARRAY_AGG(DISTINCT [record]) to aggregate them and de-duplicate.

The issue is that ARRAY_AGG produces multiple records on one of my joined tables (ENTITIES). When they are constructed using ARRAY_CONSTRUCT_COMPACT() the result is an array of arrays which I need flattened into a single array of objects.

I've tried using combinations of ARRAY_COMPACT and ARRAY_CAT with nested ARRAY_AGGS, caveat being that ARRAY_CAT only accepts 2 arguments. The below code has got me the closest to what I need but I can't seem to figure out how to FLATTEN that final array as INCLUDED.


SELECT
    a1.appointment data,
    ARRAY_CONSTRUCT_COMPACT(
        ARRAY_AGG(DISTINCT c1.call),
        ARRAY_AGG(DISTINCT e1.entity),
        ARRAY_AGG(DISTINCT a2.address)
    ) included

FROM APPOINTMENTS a1

INNER JOIN CALLS c1 ON c1.call:id = a1.appointment:callId

INNER JOIN ENTITIES e1 ON e1.entity:id IN (
    a1.appointment:relationships.agent,
    a1.appointment:relationships.consultant,
    a1.appointment:relationships.contact
)

INNER JOIN ADDRESSES a2 ON a2.address:id = a1.appointment:relationships:office 

WHERE a1.appointment:id = 'some_appointment_id'

GROUP BY a1.appointment;

The output of my INCLUDED column is currently: [[{}], [{},{}], [{}]]

Which I need flattened to: [{}, {}, {}, {}]

Any help would be greatly appreciated!

Upvotes: 2

Views: 2185

Answers (2)

snewman0008
snewman0008

Reputation: 103

A further enhancement to Bradley's answer to handle duplicates in your array, which was my specific use case when I found this!

CREATE OR REPLACE FUNCTION ARRAY_CAT_ALL( source Array )
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
    const temp_array = [].concat.apply([], SOURCE)
    const unique = new Set(temp_array); 
    return [...unique];
$$;

Upvotes: 0

Bradley
Bradley

Reputation: 2141

You can use javascript in functions that will make this nice and tidy:

CREATE OR REPLACE FUNCTION ARRAY_CAT_ALL( source Array )
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
    return [].concat.apply([], SOURCE);
$$;

Then you can wrap your ARRAY_CONSTRUCT_COMPACT result with this function which will flatten the array of arrays into a single array.

Upvotes: 5

Related Questions