Redshift Spectrum嵌套数组查询问题求助

问题场景 #

在Redshift Spectrum的表中，data列是array<struct>类型，每个单元格存储JSON对象数组，结构示例：

[{"id":1,"group":{"group_id":3,"coordinates":[23.1,23.5]}},{"id":2,"group":{"group_id":2,"coordinates":[25.1,25.5]}},{"id":5,"group":{"group_id":3,"coordinates":[24.1,24.5]}}]

通过a.data as x拆分每个JSON对象为单行后，能正常获取id和group_id，但尝试直接访问x.group.coordinates[0]/[1]时触发报错：

ARRAY type "x.group.coordinates" can only occur in the FROM or the SELECT clause

直接查询x.group也会报错：

Struct type "x.group" cannot be accessed directly. Hint: Use dot notation to access specific attributes of the struct.

解决方案 #

方案1：JSON序列化+数组元素提取 #

利用json_serialize将嵌套数组转为JSON字符串，再用json_extract_array_element_text提取指定下标的元素，最后转换为数值类型：

select 
  x.id, 
  x.group.group_id,
  -- 提取数组第一个元素作为纬度
  json_extract_array_element_text(json_serialize(x.group.coordinates), 0)::float as latitude,
  -- 提取数组第二个元素作为经度
  json_extract_array_element_text(json_serialize(x.group.coordinates), 1)::float as longitude
from table_name a, a.data as x

方案2：UNNEST数组+窗口函数转置（适配数组长度不固定场景） #

如果coordinates数组长度可能变化，可先拆分数组元素并标记顺序，再通过条件聚合转置回单行，确保不丢失顺序：

with exploded_coords as (
  select 
    x.id,
    x.group.group_id,
    coord_value,
    -- 标记每个coordinates数组中元素的原始顺序
    row_number() over (partition by x.id, x.group.group_id order by ordinal) as coord_seq
  from table_name a,
       a.data as x,
       -- 拆分数组并保留元素顺序
       unnest(x.group.coordinates) with ordinality as coords(coord_value, ordinal)
)
select 
  id,
  group_id,
  max(case when coord_seq = 1 then coord_value end) as latitude,
  max(case when coord_seq = 2 then coord_value end) as longitude
from exploded_coords
group by id, group_id

原理说明 #

Redshift Spectrum对嵌套在struct中的数组直接下标访问支持有限，报错提示明确这类数组只能作为整体在FROM（如UNNEST）或SELECT子句中处理，无法直接通过[index]取值。两种方案分别通过JSON序列化绕开直接数组访问限制，或通过UNNEST拆分后聚合，都能实现提取指定数组元素并保留每行对应id的需求。

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