如何用Jinja从Redshift的super类型字典列表提取拼接值？

问题原因分析 #

你之前的代码混用了Jinja模板语法和SQL运行时逻辑，导致temp_list为空：

• Jinja模板是在SQL语句生成阶段执行的，此时还没有查询数据，ids_array只是一个列名的字符串，不是实际的数组数据。
• 你的Jinja循环遍历的是字符串ids_array，不是查询运行时的数组元素，所以temp_list不会添加任何内容，最终返回空值。

正确解决方案 #

利用Redshift对Super类型的原生支持，通过UNNEST展开数组，再用LISTAGG聚合拼接字符串，无需依赖Jinja模板：

方法1：UNNEST + LISTAGG（推荐） #

SELECT
    main.id,
    -- 处理空数组的情况，用COALESCE转为空字符串
    COALESCE(LISTAGG(unnested_item.id::VARCHAR, ',') WITHIN GROUP (ORDER BY unnested_item.id), '') AS ids
FROM schema_name.table_name main
-- 展开tests.tests.ids数组，LEFT JOIN保留原表所有行（包括数组为空的情况）
LEFT JOIN UNNEST(main.tests.tests.ids) AS unnested_item(id) ON TRUE
GROUP BY main.id;

方法2：使用JSON函数（适合简单场景） #

如果数组长度固定或需要更细粒度控制，也可以用json_extract_path_text结合数组索引，但不适合长度可变的场景：

SELECT
    id,
    -- 手动拼接多个元素，需要根据最大长度调整
    TRIM(
        CONCAT_WS(',',
            json_extract_path_text(json_extract_array_element_text(tests.tests.ids, 0), 'id'),
            json_extract_path_text(json_extract_array_element_text(tests.tests.ids, 1), 'id'),
            json_extract_path_text(json_extract_array_element_text(tests.tests.ids, 2), 'id')
        ),
        ','
    ) AS ids
FROM schema_name.table_name;

说明 #

• UNNEST(main.tests.tests.ids)：将Super类型的数组展开为多行，每行对应一个字典元素。
• unnested_item.id：直接从展开的字典元素中提取id值（Redshift支持对Super类型的字典用.访问属性）。
• LISTAGG(...)：将同一主表id下的所有id值拼接为逗号分隔的字符串，WITHIN GROUP可选用于排序。
• COALESCE：当数组为空时，返回空字符串而非NULL。

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