Knex更新JSON列保留原有数据及插入更新异常问题求助

1. JSON列更新不丢失原有数据 #

直接覆盖JSON列会丢失原有字段，需要利用对应数据库的JSON合并函数，通过Knex的raw方法调用：

PostgreSQL（jsonb类型推荐） #

使用||运算符合并JSON对象，保留原有字段，替换新对象中同名的键：

const value = await knex("orders")
  .update({
    delivery_status: knex.raw("delivery_status || ?::jsonb", [JSON.stringify(delivery_status)]),
    source_latlong: knex.raw("source_latlong || ?::jsonb", [JSON.stringify(source_latlong)])
  })
  .where({ order_id: req.params.order_id });

如果仅需更新JSON中的特定字段，可用jsonb_set精准修改：

// 例：更新delivery_status中的status字段
const value = await knex("orders")
  .update({
    delivery_status: knex.raw("jsonb_set(delivery_status, '{status}', ?)", [JSON.stringify(newStatus)])
  })
  .where({ order_id: req.params.order_id });

MySQL #

使用JSON_MERGE_PATCH函数合并对象，相同键会被新值覆盖，不同键保留：

const value = await knex("orders")
  .update({
    delivery_status: knex.raw("JSON_MERGE_PATCH(delivery_status, ?)", [JSON.stringify(delivery_status)]),
    source_latlong: knex.raw("JSON_MERGE_PATCH(source_latlong, ?)", [JSON.stringify(source_latlong)])
  })
  .where({ order_id: req.params.order_id });

若仅修改特定字段，用JSON_SET：

// 例：更新source_latlong中的lng字段
const value = await knex("orders")
  .update({
    source_latlong: knex.raw("JSON_SET(source_latlong, '$.lng', ?)", [newLng])
  })
  .where({ order_id: req.params.order_id });

2. insert加where生成新记录的问题 #

insert语句的核心是插入新行，Knex中给insert附加where条件不会修改已有记录，只会尝试插入符合条件的新行（若order_id是主键/唯一键会触发冲突）。

正确更新已有记录 #

直接使用update语句：

const updateResult = await knex("orders")
  .update({
    route_latlong: JSON.stringify(source_latlong)
  })
  .where({ order_id: req.params.order_id });

实现“存在则更新，不存在则插入” #

如果需要这个逻辑，根据数据库类型用对应语法：

PostgreSQL

const upsertResult = await knex("orders")
  .insert({
    order_id: req.params.order_id,
    route_latlong: JSON.stringify(source_latlong),
    // 其他必填字段需补充
  })
  .onConflict("order_id")
  .merge({ route_latlong: JSON.stringify(source_latlong) });

MySQL

const upsertResult = await knex("orders")
  .insert({
    order_id: req.params.order_id,
    route_latlong: JSON.stringify(source_latlong),
    // 其他必填字段需补充
  })
  .onDuplicateKeyUpdate({
    route_latlong: JSON.stringify(source_latlong)
  });

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