如何在雪花选择中展平一组ARRAY_AGGS

Question

我正在尝试对多个连接的变体列表进行选择。主记录作为 DATA 返回，围绕它的所有支持信息都由作为 INCLUDE 返回的支持联接表组成。

我正在对支持的 VARIANT 记录和ARRAY_AGG(不同的 [记录])使用ARRAY_CONSTRUCT_COMPACT来聚合它们并删除重复数据。

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

我尝试将ARRAY_COMPACT和ARRAY_CAT与嵌套ARRAY_AGGS结合使用，需要注意的是ARRAY_CAT只接受 2 个参数。下面的代码让我最接近我需要的，但我似乎无法弄清楚如何将最终数组展平为包含。

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;

我的 INCLUDE 列的输出当前为：[[{}], [{},{}], [{}]]

我需要将其展平以：[{}, {}, {}, {}]

任何帮助将不胜感激！

Answer 1

你可以在函数中使用javascript，这将使它变得漂亮和整洁：

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

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

Answer 2

进一步增强了 Bradley 处理数组中重复项的答案，这是我发现这个时的特定用例！

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