Redshift中拆分JSON数组housing字段至多行的优化方案咨询

需求说明 #

需要将Redshift表中JSON数组内的housing字段拆分到独立行，每行需包含以下字段：

• id（表主键）
• housing.id
• housing.name
• user.id
• hours
• isPlanningTime

示例JSON数组：

[{"housing":{"id":"02d3ca45-36b3-4114-91b7-39ed12e1548b","name":"texting"},"user":{"id":"75bd4cad-acc9-420d-9d5e-4d2851a4b9c4","name":"person_person","jobTitle":"Sales Manager","avatar":null,"email":"sales@123.co.uk","disabled":false},"hours":4,"isPlanningTime":false},{"housing":{"id":"02d3ca45-36b3-4114-91b7-39ed12e1548b","name":"testing"},"user":null,"hours":4,"isPlanningTime":false},{"housing":{"id":"02d3ca45-36b3-4114-91b7-39ed12e1548b","name":"testing"},"user":null,"hours":4,"isPlanningTime":false},{"housing":{"id":"02d3ca45-36b3-4114-91b7-39ed12e1548b","name":"testing"},"user":null,"hours":4,"isPlanningTime":false}]

原实现方式通过UNION ALL手动指定数组索引，当数组元素超过100个时操作繁琐，需更高效方案。

优化方案 #

利用Redshift的JSON解析与数组展开函数，无需手动遍历索引，一次性处理任意长度的JSON数组：

方案1（Redshift 1.0.2368及以上版本） #

SELECT 
    cd.id,
    json_extract_path_text(item, 'housing', 'id') AS housing_id,
    json_extract_path_text(item, 'housing', 'name') AS housing_name,
    json_extract_path_text(item, 'user', 'id') AS user_id,
    json_extract_path_text(item, 'hours')::INT AS hours,
    json_extract_path_text(item, 'isPlanningTime')::BOOLEAN AS isPlanningTime
FROM 
    "data" cd
LEFT JOIN 
    "field" c ON c.id = cd.field_id
-- 将JSON字符串转为数组并拆分为单行
CROSS JOIN UNNEST(json_parse(cd.value)) AS t(item)
WHERE 
    c.name = 'time'

方案2（兼容低版本Redshift） #

若不支持json_parse，可使用json_array_elements_text替代：

SELECT 
    cd.id,
    json_extract_path_text(item, 'housing', 'id') AS housing_id,
    json_extract_path_text(item, 'housing', 'name') AS housing_name,
    json_extract_path_text(item, 'user', 'id') AS user_id,
    json_extract_path_text(item, 'hours')::INT AS hours,
    json_extract_path_text(item, 'isPlanningTime')::BOOLEAN AS isPlanningTime
FROM 
    "data" cd
LEFT JOIN 
    "field" c ON c.id = cd.field_id
CROSS JOIN json_array_elements_text(cd.value) AS t(item)
WHERE 
    c.name = 'time'

关键说明 #

• json_parse(cd.value)：将存储的JSON字符串转换为Redshift可识别的JSON数组类型
• UNNEST/json_array_elements_text：自动将数组中的每个元素拆分为独立行，无需手动指定索引
• 类型转换：根据实际数据类型，将hours转为INT、isPlanningTime转为BOOLEAN，确保字段类型匹配

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