如何在RedShift中从JSON数组提取指定字段生成新数组

问题描述 #

我有一张名为table的表，其中包含super类型的scans列，该列的值示例为[{"A": 1}, {"A": 2}]、[{"A": 3}, {"A": 4}, {"A": 5}]。我希望生成一列，其值为[1, 2]、[3, 4, 5]这类形式。

尝试的查询语句：

SELECT
    scans[r]."A"
FROM table t
cross join (select row_number() OVER (ORDER BY true) r from some_table) x
where r < get_array_length(t.scans)

报错信息：

[XX000] ERROR: Query unsupported due to an internal error. 
Detail: SQL reparse error. Where: function get_array_path(super, bigint) does not exist.

解决方案 #

你之前的写法不符合Redshift处理Super类型数组的逻辑，正确的方式是通过UNNEST拆分数组元素，再聚合提取目标字段：

标准写法（使用主键分组） #

假设表有主键列（比如id），用它来确保聚合对应到原始行：

SELECT
    t.id,
    array_agg(scan_item."A") AS extracted_A
FROM "table" t
CROSS JOIN UNNEST(t.scans) AS scan_item
GROUP BY t.id;

无主键临时写法 #

如果表没有主键，可临时用ctid分组（注意：ctid是系统列，不建议长期依赖）：

SELECT
    t.ctid,
    array_agg(scan_item."A") AS extracted_A
FROM "table" t
CROSS JOIN UNNEST(t.scans) AS scan_item
GROUP BY t.ctid;

原理说明 #

• UNNEST(t.scans)将Super类型的数组拆分为单行的对象元素
• array_agg(scan_item."A")把拆分后每行的A值重新聚合成数组，得到你需要的格式
• 分组列必须存在，保证每个原始行的聚合结果正确对应

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