如何在Redshift中提取嵌套JSON数组并创建指定列的表

要将Redshift表中字符串类型的嵌套JSON列扁平化，提取rows数组内的字段创建新表，可以通过Redshift的JSON函数结合序列生成来实现，具体步骤如下：

前提说明 #

首先修正你提供的示例JSON语法错误（原示例中"step": "rows": [存在语法问题，假设正确的JSON结构是包含顶级rows数组，如下）：

{
    "Id": 888,
    "Command": "uuuu",
    "rows": [
        {"sid": 1515, "skill_name": "jjjj", "skill_vdd": 9999},
        {"sid": 333, "skill_name": "ttt", "skill_vdd": 333}
    ]
}

如果你的rows数组实际嵌套在step字段下（比如"step": {"rows": [...]}），只需调整SQL中的JSON路径即可。

实现SQL #

假设原表名为original_table，要创建的新表名为new_skill_table，执行以下语句：

CREATE TABLE new_skill_table AS
WITH json_data AS (
    SELECT 
        json AS json_str,
        -- 获取rows数组的元素数量
        json_array_length(json::json, 'rows') AS array_length
    FROM original_table
),
index_series AS (
    SELECT 
        json_str,
        -- 生成数组索引（JSON数组从0开始）
        generate_series(0, array_length - 1) AS idx
    FROM json_data
    WHERE array_length > 0 -- 过滤无有效rows数据的行
)
SELECT
    -- 提取sid并转为整数类型
    json_extract_path_text(json_extract_array_element_text(json_str::json, 'rows', idx), 'sid')::INT AS sid,
    -- 提取skill_name字符串
    json_extract_path_text(json_extract_array_element_text(json_str::json, 'rows', idx), 'skill_name') AS skill_name,
    -- 提取skill_vdd并转为整数类型
    json_extract_path_text(json_extract_array_element_text(json_str::json, 'rows', idx), 'skill_vdd')::INT AS skill_vdd
FROM index_series;

关键函数说明 #

• json::json：将字符串类型的json列显式转换为Redshift支持的JSON类型。
• json_array_length：计算指定JSON路径下数组的元素个数，用于确定需要展开的行数。
• generate_series：生成连续的索引值，遍历数组中的每个元素。
• json_extract_array_element_text：根据索引提取数组中的单个JSON对象（返回字符串格式）。
• json_extract_path_text：从单个JSON对象中提取指定键对应的值。

特殊情况处理 #

如果原表中存在rows数组为null或空数组的行，WHERE array_length > 0会自动过滤这些行，避免生成无效数据。如果需要保留这些行（字段值为null），可以移除该过滤条件。

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