PostgreSQL：如何复制jsonb字段值至新字段并修正标量提取错误？

问题原因 #

你使用的jsonb_array_elements是用于遍历JSON数组的函数，但你的Details->'Type'是一个标量值（字符串"VIP"），并非数组类型。这个函数无法从标量中提取数组元素，因此触发了"Can't extract scalar value"错误。

解决方案 #

以下是几种可行的更新方法，都能实现你想要的效果：

方法1：嵌套调用jsonb_set #

先添加Subscription字段，再修改Type的值：

UPDATE "Media"
SET "Details" = jsonb_set(
    -- 第一步：新增Subscription字段，值为原Type的内容
    jsonb_set("Details", '{Subscription}', "Details"->'Type', true),
    -- 第二步：修改Type字段为2
    '{Type}', '2'::jsonb, false
);

• jsonb_set的第三个参数直接传入Details->'Type'即可，它本身就是符合要求的jsonb标量值，无需额外处理。
• 最后一个参数true表示允许新增字段，false表示仅修改已存在的字段。

方法2：使用jsonb合并操作符|| #

通过合并新的键值对来实现更新，更简洁：

UPDATE "Media"
SET "Details" = 
    -- 新增Subscription字段
    ("Details" || jsonb_build_object('Subscription', "Details"->'Type'))
    -- 修改Type字段为2
    || jsonb_build_object('Type', 2);

• jsonb_build_object可以安全地构造jsonb对象，避免手动拼接字符串带来的转义问题。
• ||操作符会自动合并两个jsonb对象，重复的键会以右侧的值为准。

方法3：直接构造完整的jsonb对象 #

如果表中Details的字段较少，直接构造新对象更直观：

UPDATE "Media"
SET "Details" = jsonb_build_object(
    'Type', 2,
    'Quantity', "Details"->'Quantity',
    'Subscription', "Details"->'Type'
);

• 这种方式明确指定每个字段的值，可读性强，适合字段固定且数量不多的场景。

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