Redshift如何将JSON列表内多个值拆分输出为独立行

问题原因 #

你的原有查询存在两个核心问题：

• 仅提取了inputs数组的第0位元素，无法处理单条记录中存在多个输入name的场景
• 没有对values数组做展开操作，所以所有value值都以JSON数组字符串的形式存在同一行，无法拆分为独立记录

实现方案 #

Redshift中可以通过generate_series生成数组索引，两级展开分别处理inputs数组和每个输入项的values数组，实现需求：

WITH 
-- 第一步：展开inputs数组，每个输入name对应一行
expanded_inputs AS (
    SELECT
        t.projectid,
        t.prompttype,
        t.scriptid,
        t.corpuscode,
        -- 提取当前索引下的输入项属性
        json_extract_path_text(json_extract_array_element_text(t.inputs, input_idx.idx, TRUE), 'name') AS input_name,
        json_extract_path_text(json_extract_array_element_text(t.inputs, input_idx.idx, TRUE), 'desc') AS input_desc,
        json_extract_path_text(json_extract_array_element_text(t.inputs, input_idx.idx, TRUE), 'values') AS value_json_array
    FROM source.table t
    -- 生成inputs数组的所有索引，长度为数组长度，索引从0开始
    JOIN generate_series(0, json_array_length(t.inputs) - 1) AS input_idx(idx)
        ON input_idx.idx < json_array_length(t.inputs)
    WHERE t.prompttype = 'input'
      AND json_array_length(t.inputs) > 0 -- 过滤空inputs的记录
),
-- 第二步：展开每个输入项的values数组，每个value对应一行
expanded_values AS (
    SELECT
        ei.projectid,
        ei.prompttype,
        ei.scriptid,
        ei.corpuscode,
        ei.input_name,
        ei.input_desc,
        -- 提取当前索引下的value值
        json_extract_array_element_text(ei.value_json_array, value_idx.idx, TRUE) AS input_value,
        -- 生成唯一标识，根据需求选择即可
        uuid() AS prompt_input_value_id
    FROM expanded_inputs ei
    -- 生成values数组的所有索引
    JOIN generate_series(0, json_array_length(ei.value_json_array) - 1) AS value_idx(idx)
        ON value_idx.idx < json_array_length(ei.value_json_array)
    WHERE json_array_length(ei.value_json_array) > 0 -- 过滤空values的记录
)
-- 查询结果，也可以直接写INSERT INTO 目标表 SELECT ... 插入数据
SELECT 
    'ProjectId : ' || projectid || '. Input value = ' || input_value AS output_line,
    prompt_input_value_id,
    projectid,
    input_name,
    input_desc,
    input_value,
    scriptid,
    corpuscode
FROM expanded_values;

插入到目标表的写法 #

如果需要直接写入dim_collect_user_inp_configs表，将最后一步的SELECT替换为INSERT语句即可：

INSERT INTO dim_collect_user_inp_configs (
    prompt_input_value_id,
    projectid,
    input_name,
    input_desc,
    input_value,
    scriptid,
    corpuscode
)
SELECT 
    prompt_input_value_id,
    projectid,
    input_name,
    input_desc,
    input_value,
    scriptid,
    corpuscode
FROM expanded_values;

注意事项 #

• 如果你的Redshift版本支持JSON_TABLE语法，可以更简洁的完成数组展开，性能也更优
• 若不需要空value，可以在最终WHERE条件中添加input_value != ''过滤
• prompt_input_value_id如果用自增列，建表时将该字段设置为IDENTITY(1,1)即可，插入时不需要在SELECT中包含该字段

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