Redshift中SUPER数组提取value与dateTime至表格的技术问询

背景 #

通过REST API导入Redshift的API_table中，values列为嵌套JSON数组（SUPER类型），需将所有行中的value和dateTime字段提取至新的结构化表。

完整解决方案 #

1. 确认嵌套结构（可选） #

执行以下查询查看示例数据的JSON结构，确保层级匹配：

SELECT json_serialize(values) FROM API_table LIMIT 1;

2. 编写完整展开查询 #

通过两层横向展开处理嵌套数组，提取所有目标字段：

WITH expanded AS (
    -- 展开第一层values数组
    SELECT outer_val
    FROM API_table, API_table.values outer_val
),
final_expanded AS (
    -- 展开第二层value数组，提取value和dateTime
    SELECT 
        inner_val.value AS metric_value,
        inner_val."dateTime" AS metric_datetime
    FROM expanded, expanded.outer_val.value inner_val
)
SELECT * FROM final_expanded;

3. 创建结构化新表 #

使用CTAS（CREATE TABLE AS）直接生成目标表，同时可指定数据类型和表属性优化性能：

-- 显式指定字段类型的创建方式（推荐）
CREATE TABLE structured_metrics (
    metric_value DOUBLE PRECISION, -- 根据实际数据类型调整，如VARCHAR、INT等
    metric_datetime TIMESTAMP
)
WITH (
    DISTSTYLE AUTO,
    SORTKEY (metric_datetime) -- 根据查询模式设置排序键
) AS
WITH expanded AS (
    SELECT outer_val
    FROM API_table, API_table.values outer_val
),
final_expanded AS (
    SELECT 
        CAST(inner_val.value AS DOUBLE PRECISION) AS metric_value,
        -- 若dateTime是字符串格式，可指定转换格式，例如：TO_TIMESTAMP(inner_val."dateTime", 'YYYY-MM-DD HH24:MI:SS')
        CAST(inner_val."dateTime" AS TIMESTAMP) AS metric_datetime
    FROM expanded, expanded.outer_val.value inner_val
)
SELECT * FROM final_expanded;

注意事项 #

• 类型适配：根据实际业务数据调整CAST的目标类型，避免类型不匹配报错
• 空值处理：若存在空字段或空数组元素，可使用COALESCE兜底，例如COALESCE(CAST(inner_val.value AS INT), 0)
• 性能优化：针对大表场景，合理设置分布键（DISTKEY）和排序键（SORTKEY），提升后续查询效率

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