Postgres中如何用单查询实现jsonb列数组的创建或追加

可以通过嵌套jsonb_set和jsonb_insert，或者更简洁的COALESCE+数组拼接语法，在一次UPDATE中完成需求：

方法一：嵌套jsonb_set与jsonb_insert #

UPDATE transactions
SET body = jsonb_insert(
    -- 先确保refunds键存在，无则初始化为空数组
    jsonb_set(body, '{refunds}', '[]'::jsonb, true),
    -- 在refunds数组末尾插入新元素（-1表示数组最后位置之后）
    '{refunds,-1}',
    '{"id":"any"}'::jsonb,
    true
)
WHERE transaction_id = 'abc';

原理说明 #

• 内层jsonb_set的第四个参数true表示：如果refunds键不存在，则创建该键并赋值为空数组；如果已存在，则保留原数组内容。
• 外层jsonb_insert使用{refunds,-1}路径，指定在数组的末尾插入新元素，确保追加操作的正确性。

方法二：COALESCE+数组拼接（更简洁） #

UPDATE transactions
SET body = jsonb_set(
    body,
    '{refunds}',
    -- 用COALESCE获取现有数组，无则用空数组，再拼接新元素
    COALESCE(body->'refunds', '[]'::jsonb) || '{"id":"any"}'::jsonb,
    true
)
WHERE transaction_id = 'abc';

原理说明 #

• COALESCE(body->'refunds', '[]'::jsonb)：如果body中没有refunds键，返回空数组；否则返回原数组。
• PostgreSQL的jsonb数组支持用||操作符拼接，直接将新元素追加到数组末尾。
• jsonb_set负责将拼接后的数组写回body的refunds键，确保键不存在时自动创建。

这两种写法都能原子性地完成“初始化空数组（若不存在）+追加元素”的操作，避免了两次UPDATE带来的并发风险。

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