PostgreSQL更新jsonb列时如何获取当前行ID并按条件更新

单条查询即可完成全部需求 #

你最初的写法里用JS模板字符串占位ROW_ID_COLUMN_HERE的思路行不通：knex.raw的参数是在Node服务端拼接生成SQL的，拿不到查询执行时逐行的ID值，必须把ID拼接逻辑放到PostgreSQL内部逐行计算。
正确实现代码如下，同时覆盖「拼接固定字符串+当前行ID」「仅更新city属性非空的行」两个需求：

return knex("tablename").update({
  jsonbkey: knex.raw(`
    jsonb_set(
      jsonbkey,
      '{city}',
      to_jsonb('Ayodhya ' || id::text)
    )
  `)
}).where(knex.raw(`jsonbkey->>'city' <> ''`))

关键逻辑说明：

• ||是PostgreSQL原生字符串拼接符，id::text将数值型ID转为文本类型，逐行读取当前记录的ID完成拼接
• 用to_jsonb()将拼接完成的普通字符串转为jsonb类型，比手动拼双引号更稳妥，不会出现特殊字符转义问题
• 你之前写的.and.whereNot(jsonbkey.city, '')语法错误：Knex不会自动解析jsonb字段的属性路径，判断jsonb内属性的文本值需要用->>操作符取出属性的文本内容再做比较，直接写jsonbkey.city会被Knex识别为不存在的普通列名直接报错。

如果需要限定特定ID范围，直接在where链追加条件即可，比如你示例里只更新id=2020的行：

return knex("tablename").update({
  jsonbkey: knex.raw(`
    jsonb_set(
      jsonbkey,
      '{city}',
      to_jsonb('Ayodhya ' || id::text)
    )
  `)
}).where("id", 2020)
.andWhere(knex.raw(`jsonbkey->>'city' <> ''`))

两次查询+遍历更新的方案不合理 #

不建议用先聚合ID再逐个更新的方案，核心问题有三个：

• 性能损耗大：如果符合条件的行数较多，会产生多次数据库请求，网络IO开销远高于单条批量更新
• 数据一致性风险：两次查询的间隔期如果有数据变动（比如某行city被修改为空、行被删除），会出现更新错误
• 代码冗余：单条SQL即可实现全部逻辑，额外的聚合、遍历逻辑完全没有必要

针对你给出的测试样例数据，执行上述单条更新后的结果符合预期：

• id=1的行city值为空字符串，不会被更新，保留原值{"city": "", "code": "EU"}
• id=2的行会被更新为{"city": "Ayodhya 2", "code": "FR"}

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