PostgreSQL中JSONB列：从字符串数组迁移至对象数组

直接用一条UPDATE语句就能完成所有元素的转换，无需逐个索引手动处理：

UPDATE public.metadata
SET metadata = jsonb_set(
    -- 确保items对象存在，不存在则创建空对象
    COALESCE(metadata -> 'items', '{}'::jsonb) || metadata,
    '{items, umbrella}',
    -- 处理Umbrella数组，生成目标对象数组
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'color', split_part(elem.value::text, '|', 1),
                'pattern', split_part(elem.value::text, '|', 2)
            )
        )
        FROM jsonb_array_elements(metadata -> 'Umbrella') AS elem
    )::jsonb,
    -- 若umbrella键已存在则覆盖
    true
);

关键逻辑拆解： #

• jsonb_array_elements(metadata -> 'Umbrella')：将原数组的每个元素拆分为独立行，实现遍历所有元素的效果。
• split_part(elem.value::text, '|', 1/2)：按|分割字符串，分别提取颜色和图案值。
• jsonb_build_object('color', ..., 'pattern', ...)：把拆分后的字符串组装成目标格式的JSON对象。
• jsonb_agg(...)：将处理后的单个对象重新聚合为数组。
• jsonb_set(..., true)：第三个参数设为true，表示items->umbrella存在则覆盖、不存在则创建。

可选优化：过滤无效行 #

如果表中存在Umbrella字段为空或非数组的情况，可添加WHERE条件避免无效更新：

UPDATE public.metadata
SET metadata = jsonb_set(
    COALESCE(metadata -> 'items', '{}'::jsonb) || metadata,
    '{items, umbrella}',
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'color', split_part(elem.value::text, '|', 1),
                'pattern', split_part(elem.value::text, '|', 2)
            )
        )
        FROM jsonb_array_elements(metadata -> 'Umbrella') AS elem
    )::jsonb,
    true
)
WHERE metadata ? 'Umbrella' -- 仅处理存在Umbrella字段的行
  AND jsonb_typeof(metadata -> 'Umbrella') = 'array'; -- 确保字段是数组类型

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