如何使用pg-promise helpers实现多行更新时jsonb字段的局部更新

你现有代码的问题主要有两点：

1. 直接对other字段赋值，会完全覆盖原有内容，没有做jsonb合并操作。
2. dataMulti中字段名写错为others，和列定义中的other不匹配，会导致更新值读取异常。

你需要借助PostgreSQL原生的jsonb合并操作符||实现局部更新，该操作符会保留原jsonb中的所有键，仅覆盖你传入的新键值对，不会改动其他属性。

方案1：批量更新（每行可自定义更新内容） #

适合10个Jane用户需要不同更新内容的场景，推荐用主键id匹配避免误操作：

// column sets
const cs = new pgp.helpers.ColumnSet(
  ['?id', {
    name: 'other',
    cast: 'jsonb',
    // 自定义赋值逻辑，合并原有other和新的jsonb内容
    init: col => pgp.as.format('other || $1::jsonb', [col.value])
  }],
  { table: 'person' }
);

// values sets，注意字段名和列定义一致为other
const dataMulti = mydatas.map(d => ({
  id: d.id,
  other: { gender: 'fem' }
}));

// 生成更新语句，通过id匹配目标行
const query = pgp.helpers.update(dataMulti, cs) + ' WHERE v.id = t.id';
return db.none(query);

方案2：全量更新所有Jane用户 #

如果所有name为Jane的用户都要统一修改gender，不需要逐行定制，直接用单条SQL性能更高：

return db.none(
  `UPDATE person SET other = other || $1::jsonb WHERE name = $2`,
  [{ gender: 'fem' }, 'Jane']
);

如果你确实需要用name作为匹配条件做批量更新，只需要把ColumnSet中的id换成?name，WHERE条件改为v.name = t.name即可，注意该操作会更新所有name匹配的行，提前确认数据范围避免误操作。

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