MongoDB跨集合更新失败：按关联条件更新user_businesses字段

集合示例数据 #

users集合 #

{
  "_id": ObjectId("64a6b0f5af3af7f59a05ba33"),
  "id": 134,
  "display_id": "PRM-SRL-000001348",
  "client_emp_code": "",
  "entity_code": "",
  "user_type": "candidate",
  "role": "",
  "user_account_type": "",
  "business_id": 104,
  "parent_id": 94
}

user_businesses集合 #

{
  "_id": ObjectId("64a6b0f5a488ab4bc843d331"),
  "id": 31,
  "business_id": 196,
  "contact_person": "",
  "phone_code": 1,
  "phone_iso": "us"
}

需求说明 #

基于users.id = user_businesses.business_id的关联条件，将users集合对应文档的_id更新到user_businesses集合的business_id字段（作为外键）。

原错误代码 #

db.users.find().forEach(function(userDoc) {
  var matchingBusinessDoc = db.user_businesses.findOne({ business_id: userDoc.id });
  if (matchingBusinessDoc) {
    db.user_businesses.updateOne(
      { business_id: userDoc.id },
      { $set: { business_id: userDoc._id: ObjectId } }
    );
  }
});

问题分析与修正 #

原代码核心错误是$set子句的语法错误：{ business_id: userDoc._id: ObjectId }属于无效JSON结构，userDoc._id本身就是ObjectId类型，无需额外声明类型。同时，多余的findOne查询可以省略，直接通过updateOne的条件匹配完成操作。

修正后的基础版本 #

db.users.find().forEach(function(userDoc) {
  db.user_businesses.updateOne(
    { business_id: userDoc.id },
    { $set: { business_id: userDoc._id } }
  );
});

高效批量更新版本（适合大数据量） #

如果集合数据量较大，推荐使用聚合管道批量更新，避免循环遍历的性能损耗：

db.user_businesses.updateMany(
  {},
  [
    {
      $lookup: {
        from: "users",
        localField: "business_id",
        foreignField: "id",
        as: "matchingUser"
      }
    },
    { $match: { matchingUser: { $ne: [] } } },
    {
      $set: {
        business_id: { $arrayElemAt: ["$matchingUser._id", 0] }
      }
    },
    { $unset: "matchingUser" }
  ]
);

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