在Redshift中通过物化视图提取DynamoDB嵌套JSON字段

步骤1：修正DynamoDB JSON格式 #

你的示例JSON存在格式错误：DynamoDB AttributeValue的类型键需为大写（如字符串类型用S而非s），修正后的JSON如下：

{"Name":{"L":[{"M":{"Field1":{"S": "TRUE"},"Field2":{"S": "FALSE"},"Field3":{"S": "TRUE"}}}]}}

步骤2：创建物化视图的SQL语句 #

假设你的源表名为dynamodb_raw_data，存储JSON的列名为json_payload，以下SQL可创建物化视图并提取目标字段：

CREATE MATERIALIZED VIEW flattened_dynamodb_data AS
SELECT
    -- 提取每个字段的字符串值
    json_extract_path_text(item.value, 'M', 'Field1', 'S') AS field1,
    json_extract_path_text(item.value, 'M', 'Field2', 'S') AS field2,
    json_extract_path_text(item.value, 'M', 'Field3', 'S') AS field3
FROM
    dynamodb_raw_data,
    -- 解析JSON并展开Name下的列表元素
    UNNEST(json_parse(json_payload)->'Name'->'L') AS item(value);

关键说明 #

• json_parse(json_payload)：将字符串格式的JSON解析为JSON对象
• ->'Name'->'L'：定位到JSON中Name字段对应的列表（DynamoDB的L类型）
• UNNEST(...)：将列表中的每个元素展开为单独行，兼容列表包含多个元素的场景
• json_extract_path_text(...)：逐层定位到目标字段，最终提取S类型对应的字符串值

如果确认Name->L列表固定只有一个元素，可使用简化写法（仅适用于固定长度列表）：

CREATE MATERIALIZED VIEW flattened_dynamodb_data AS
SELECT
    json_extract_path_text(json_parse(json_payload), 'Name', 'L', '0', 'M', 'Field1', 'S') AS field1,
    json_extract_path_text(json_parse(json_payload), 'Name', 'L', '0', 'M', 'Field2', 'S') AS field2,
    json_extract_path_text(json_parse(json_payload), 'Name', 'L', '0', 'M', 'Field3', 'S') AS field3
FROM dynamodb_raw_data;

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