如何将Postgresql中带有多参数的ARRAY_AGG移植到Snowflake

Question

Snowflake支持ARRAY_AGG但它只能接受参数，而Postgresql的版本支持多个参数。

我需要将 Posgresql 中的以下代码片段移植到 Snowflake：

ARRAY_AGG(state, city, zipcode)

其中州、城市和邮政编码是我的一个表中的字段。

有什么解决方法吗？我知道我可以创建 3 个单独的字段，但这不是需要的。

Answer 1

取决于你想要数组数组还是对象数组

WITH r AS (
SELECT column1 AS A, column2 AS B FROM (VALUES (1,'A'),(14,'B'),(35,'C'),(91,'D'),(105,'E'))
)
SELECT ARRAY_AGG(ARRAY_CONSTRUCT(a,b)) FROM r;

给：

[ [ 1, "A" ],  [ 14, "B" ], [ 35, "C" ], [ 91, "D" ], [ 105, "E" ] ]

或

WITH r AS (
SELECT column1 AS A, column2 AS B FROM (values (1,'A'),(14,'B'),(35,'C'),(91,'D'),(105,'E'))
)
SELECT ARRAY_AGG(OBJECT_CONSTRUCT('A',a,'B',b)) FROM r;

给：

[ { "A": 1, "B": "A" }, { "A": 14, "B": "B" }, { "A": 35, "B": "C" }, { "A": 91, "B": "D" }, { "A": 105, "B": "E" } ]

https://docs.snowflake.net/manuals/sql-reference/functions/array_agg.html https://docs.snowflake.net/manuals/sql-reference/functions/array_construct.html https://docs.snowflake.net/manuals/sql-reference/functions/object_construct.html