Redshift中JSON扁平化并写入固定结构目标表的实现求助

给定JSON数据：

{
    "test": {        
        "userId": 77777,
        "sectionScores": [
            {
                "id": 2,
                "score": 244
            },
            {
                "id": 1,
                "score": 212
            }
        ]
    }
}

注意：sectionScores的顺序不固定。
id 1代表sectionname1，id 2代表sectionname2，id 3代表sectionname3，最多返回4个section，最少可能返回2个。

已在Redshift中创建stage表：

create table stage.poc1(
user_id bigint ,
json_data super
)

需要编写扁平化查询，将数据插入至如下结构的target表：

create table target.poc
(
user_id bigint,
sectionname1_score int,
sectionname2_score int,
sectionname3_score int,
sectionname4_score int
)

需求是动态匹配sectionScores中的id与target表对应列赋值，求对应的INSERT语句写法。

方法一：条件聚合（直观易读） #

通过CASE语句匹配section的id与目标列，结合聚合函数将多行合并为一行：

INSERT INTO target.poc (user_id, sectionname1_score, sectionname2_score, sectionname3_score, sectionname4_score)
SELECT
    s.user_id,
    MAX(CASE WHEN ss.id = 1 THEN ss.score END) AS sectionname1_score,
    MAX(CASE WHEN ss.id = 2 THEN ss.score END) AS sectionname2_score,
    MAX(CASE WHEN ss.id = 3 THEN ss.score END) AS sectionname3_score,
    MAX(CASE WHEN ss.id = 4 THEN ss.score END) AS sectionname4_score
FROM stage.poc1 s
LEFT JOIN s.json_data.test.sectionScores ss ON TRUE
GROUP BY s.user_id;

说明：

• LEFT JOIN ... ON TRUE用于展开SUPER类型的sectionScores数组，将数组内的每个对象拆分为单独行
• CASE语句根据section的id映射到目标表对应列，无匹配id的列自动填充为NULL
• MAX聚合函数将同一用户的多行数据合并为一行，避免重复

方法二：PIVOT语法（简洁规范） #

先拆分数组得到行数据，再通过PIVOT将行转列匹配目标表结构：

INSERT INTO target.poc (user_id, sectionname1_score, sectionname2_score, sectionname3_score, sectionname4_score)
SELECT
    user_id,
    "1" AS sectionname1_score,
    "2" AS sectionname2_score,
    "3" AS sectionname3_score,
    "4" AS sectionname4_score
FROM (
    -- 拆分数组获取每个section的id和score
    SELECT
        s.user_id,
        ss.id,
        ss.score
    FROM stage.poc1 s
    LEFT JOIN s.json_data.test.sectionScores ss ON TRUE
) AS source
PIVOT (
    MAX(score) FOR id IN ("1", "2", "3", "4")
) AS pivot_table;

说明：

• 子查询先展开数组，得到用户与section的关联行数据
• PIVOT根据id值将行转换为列，最后重命名为目标表的列名
• 同样，未匹配到的section列会填充为NULL

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