如何在Snowflake SQL中关联数组元素且不展开数组？

针对你提到的场景——把boxes表中contents列的ID数组替换成contents表对应的标签数组，且不破坏原数组结构，以下是主流数据库的实现方案：

PostgreSQL 实现 #

PostgreSQL原生支持数组类型，通过unnest拆分数组并保留原顺序，再聚合回标签数组：

SELECT 
    b.box_id,
    array_agg(c.label ORDER BY idx) AS contents_labels
FROM boxes b
JOIN unnest(b.contents) WITH ORDINALITY AS u(content_id, idx)
    ON u.content_id = c.content_id
JOIN contents c ON c.content_id = u.content_id
GROUP BY b.box_id;

WITH ORDINALITY会给拆出来的每个ID带上原数组的索引，聚合时按索引排序能保证标签数组和原ID数组的顺序完全一致。

MySQL 8.0+ 实现 #

MySQL 8.0及以上支持JSON数组处理，用JSON_TABLE拆分数组，再通过JSON_ARRAYAGG聚合回标签数组：

SELECT 
    b.box_id,
    JSON_ARRAYAGG(c.label ORDER BY j.idx) AS contents_labels
FROM boxes b
JOIN JSON_TABLE(
    b.contents,
    '$[*]' COLUMNS(
        content_id INT PATH '$',
        idx FOR ORDINALITY
    )
) j
JOIN contents c ON j.content_id = c.content_id
GROUP BY b.box_id;

FOR ORDINALITY用来保留原数组的位置信息，确保标签顺序和ID数组匹配。

SQL Server 实现 #

SQL Server可以用OPENJSON拆分数组，再通过聚合函数生成JSON格式的标签数组：

SELECT 
    b.box_id,
    JSON_QUERY('[' + STRING_AGG('"' + c.label + '"', ',') + ']') AS contents_labels
FROM boxes b
JOIN OPENJSON(b.contents) j
    ON CAST(j.value AS INT) = c.content_id
JOIN contents c ON c.content_id = CAST(j.value AS INT)
GROUP BY b.box_id;

或者用子查询+FOR JSON的方式更简洁，还能自动处理标签中的特殊字符：

SELECT 
    b.box_id,
    (
        SELECT c.label
        FROM OPENJSON(b.contents) j
        JOIN contents c ON CAST(j.value AS INT) = c.content_id
        ORDER BY j.[key]
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS contents_labels
FROM boxes b;

注意事项 #

• 如果ID数组中存在contents表没有匹配的ID，把JOIN换成LEFT JOIN，避免丢失整行数据，对应位置的标签会显示为NULL，可以用COALESCE(c.label, '未找到标签')替换成默认值。
• 所有方案都保留了原数组的顺序，这依赖拆分时获取的索引/位置字段，不要省略排序步骤，否则标签顺序可能混乱。

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