Redshift中如何从嵌套对象提取JSON数组并转置数据？

针对你遇到的metadata列（含嵌套数组的动态JSON对象）解析问题，以下是具体解决思路和代码示例：

核心问题分析 #

你之前的错误是误用了JSON_EXTRACT_ARRAY_ELEMENT_TEXT函数——这个函数仅用于提取JSON数组的元素，而metadata本身是JSON对象，直接调用会触发解析错误。

方案1：扁平化JSON+展开数组为多行 #

如果需要将顶层固定字段（如name、references）转为列，同时把数组字段（owners、groups、动态新增的sources）展开为多行，可以用CTE结合GENERATE_SERIES实现：

WITH parsed_meta AS (
    SELECT
        -- 替换为你的表主键，用于关联原始行
        id,
        -- 先将字符串转为JSON类型，方便后续操作
        JSON_PARSE(metadata) AS meta_json,
        -- 提取顶层非数组字段
        JSON_EXTRACT_PATH_TEXT(metadata, 'name') AS name,
        JSON_EXTRACT_PATH_TEXT(metadata, 'references') AS "references"
    FROM your_table
),
-- 展开owners数组
owners_rows AS (
    SELECT
        id,
        name,
        "references",
        JSON_EXTRACT_ARRAY_ELEMENT_TEXT(meta_json::VARCHAR, idx) AS owner
    FROM parsed_meta,
         GENERATE_SERIES(0, JSON_ARRAY_LENGTH(meta_json->'owners') - 1) AS idx
    -- 过滤空数组
    WHERE JSON_ARRAY_LENGTH(meta_json->'owners') > 0
),
-- 展开groups数组
groups_rows AS (
    SELECT
        id,
        name,
        "references",
        JSON_EXTRACT_ARRAY_ELEMENT_TEXT(meta_json::VARCHAR, idx) AS group_name
    FROM parsed_meta,
         GENERATE_SERIES(0, JSON_ARRAY_LENGTH(meta_json->'groups') - 1) AS idx
    WHERE JSON_ARRAY_LENGTH(meta_json->'groups') > 0
),
-- 处理动态新增的sources字段（自动识别是否存在）
sources_rows AS (
    SELECT
        id,
        name,
        "references",
        JSON_EXTRACT_ARRAY_ELEMENT_TEXT(meta_json::VARCHAR, idx) AS source
    FROM parsed_meta,
         GENERATE_SERIES(0, JSON_ARRAY_LENGTH(meta_json->'sources') - 1) AS idx
    WHERE JSON_EXTRACT_PATH_TEXT(meta_json::VARCHAR, 'sources') IS NOT NULL
      AND JSON_ARRAY_LENGTH(meta_json->'sources') > 0
)
-- 按需组合结果，这里用左连接保留所有原始行数据
SELECT
    pm.id,
    pm.name,
    pm."references",
    ors.owner,
    grs.group_name,
    srs.source
FROM parsed_meta pm
LEFT JOIN owners_rows ors ON pm.id = ors.id
LEFT JOIN groups_rows grs ON pm.id = grs.id
LEFT JOIN sources_rows srs ON pm.id = srs.id;

方案2：将所有JSON键值对转为行数据 #

如果需要把JSON中每个键（包括动态新增的sources）都转为单独的行，数组元素也拆分为独立行，可以用JSON_KEYS提取所有键，再结合类型判断处理数组：

WITH key_value_pairs AS (
    SELECT
        id,
        key_name,
        JSON_EXTRACT_PATH(metadata, key_name) AS raw_value
    FROM your_table,
         -- 提取JSON所有顶层键
         UNNEST(JSON_KEYS(metadata)) AS t(key_name)
)
-- 拆分数组元素为行
SELECT
    id,
    key_name,
    JSON_EXTRACT_ARRAY_ELEMENT_TEXT(raw_value::VARCHAR, idx) AS value
FROM key_value_pairs,
     GENERATE_SERIES(0, JSON_ARRAY_LENGTH(raw_value) - 1) AS idx
WHERE JSON_TYPEOF(raw_value) = 'array'
UNION ALL
-- 非数组键值对直接输出
SELECT
    id,
    key_name,
    raw_value::VARCHAR AS value
FROM key_value_pairs
WHERE JSON_TYPEOF(raw_value) != 'array';

关键函数说明 #

• JSON_PARSE：将字符串类型的JSON转为JSON对象，避免字符串解析的兼容性问题
• JSON_ARRAY_LENGTH：获取数组长度，配合GENERATE_SERIES生成数组索引，防止越界
• JSON_TYPEOF：判断JSON值的类型（数组/字符串/对象等），实现动态逻辑
• JSON_KEYS：提取JSON对象的所有顶层键，处理动态新增字段场景

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