Snowflake中如何从含OBJECT类型的ARRAY构造字符串？

要将ITEM表中ARRAY类型的TAGS列转换为仅包含Name和ID、逗号分隔的字符串，可以通过FLATTEN展开数组，结合CONCAT和LISTAGG聚合实现，具体操作如下：

核心SQL语句 #

假设你的表有唯一主键列（比如ITEM_ID），使用以下语句：

SELECT
    LISTAGG(CONCAT(t.value:Name::STRING, ': ', t.value:ID::STRING), ', ') AS TAGS
FROM ITEM
LEFT JOIN LATERAL FLATTEN(input => ITEM.TAGS) t
GROUP BY ITEM.ITEM_ID; -- 替换为实际主键列

如果表没有明确主键，也可以基于整行分组（大表不推荐此方式）：

SELECT
    LISTAGG(CONCAT(t.value:Name::STRING, ': ', t.value:ID::STRING), ', ') AS TAGS
FROM ITEM, LATERAL FLATTEN(input => ITEM.TAGS) t
GROUP BY ITEM.*;

语句说明 #

• FLATTEN: 将ARRAY类型的TAGS列拆分为多行，每行对应数组中的一个JSON对象，生成的value列就是单个对象数据。
• CONCAT: 通过value:Name::STRING、value:ID::STRING提取对象中的属性并转为字符串，拼接成"Name: ID"的格式。
• LISTAGG: 按原表主键（或整行）分组，将每组内的拼接结果用, 分隔合并为一个完整字符串。

输出结果 #

执行后会生成你期望的格式：

内容的提问来源于stack exchange，提问作者Long Luu