如何在Snowflake SELECT中对一组ARRAY_AGGS进行FLATTEN [英] How to FLATTEN a set of ARRAY_AGGS in Snowflake SELECT

问题描述

我正在尝试对多个联接的VARIANT列表进行SELECT.主记录将以DATA的形式返回，并且围绕它的所有支持信息都将以INCLUDED的形式返回支持的连接表.

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.

我在支持的VARIANT记录和ARRAY_AGG(DISTINCT [record])上使用ARRAY_CONSTRUCT_COMPACT对其进行汇总和重复数据删除.

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

问题是ARRAY_AGG在我的一个联接表(ENTITIES)上产生了多个记录.当使用ARRAY_CONSTRUCT_COMPACT()构造它们时，结果是一个数组数组，我需要将其展平为单个对象数组.

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.

我尝试将ARRAY_COMPACT和ARRAY_CAT与嵌套的ARRAY_AGGS结合使用，警告是ARRAY_CAT仅接受2个参数.下面的代码使我最接近需要的代码，但是我似乎无法弄清楚如何将最后一个数组填充为INCLUDED.

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;

我的INCLUDED列的输出当前为: [[[{}]，[{}，{}]，[{}]]

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

我需要扁平化到: [{}，{}，{}，{}]

任何帮助将不胜感激！

推荐答案

您可以在功能强大且整洁的函数中使用javascript:

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);
$$;

然后，您可以使用此函数包装ARRAY_CONSTRUCT_COMPACT结果，该结果会将数组数组展平为单个数组.

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

这篇关于如何在Snowflake SELECT中对一组ARRAY_AGGS进行FLATTEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！