如何将Redshift中SUPER类型数组字段转为行数据？

问题背景 #

现有Redshift表my_schema.my_table，结构及数据如下：

id   col1   my_list
-------------------
1    true   [{"id": "A", "name": "foo"}, {"id": "B", "name": "bar"}]

其中my_list为SUPER类型数组，需要将其动态展开为如下结构：

id   name
---------
A    foo
B    bar

尝试以下SQL时出现报错：

SELECT
  my_list[idx]."id",
  my_list[idx]."name"
FROM my_schema.my_table
CROSS JOIN (
  SELECT generate_series(0, GET_ARRAY_LENGTH(my_list) - 1) AS idx
  FROM my_schema.my_table
) AS idx_table

报错信息：[0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

错误原因 #

Redshift不支持在关联子查询中同时使用generate_series和SUPER类型相关函数（如GET_ARRAY_LENGTH），这类组合不符合Redshift的表扫描逻辑。

可行实现方法 #

使用Redshift内置的UNNEST函数直接展开SUPER数组，无需手动生成索引：

SELECT
  element."id" AS id,
  element."name" AS name
FROM my_schema.my_table,
     UNNEST(my_list) AS element

说明 #

• UNNEST(my_list)会将数组中的每个JSON对象拆分为单独行，element为每个数组元素的别名
• 直接通过element."id"、element."name"提取JSON属性，自动适配任意长度的数组
• 该方法是Redshift处理SUPER数组展开的原生方案，性能更优且语法简洁

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