MongoDB带pipeline的$lookup报错：无法将数组转为ObjectId

问题描述 #

使用常规$lookup关联查询可正常执行，但改用带pipeline的写法时，触发错误：

"Unsupported conversion from array to objectId in $convert with no onError value"

常规可运行代码 #

{
  $lookup: {
    from: "reserves",
    localField: "reserve",
    foreignField: "_id",
    as: "reserve"
  }
}

常规代码运行结果 #

{
  "status": "success",
  "message": "okkkk",
  "results": 1,
  "data": [
    {
      "_id": "sdfgsd56",
      "customer": "fghdfbv",
      "price": 170000,
      "off": 30000,
      "reserve": [
        {
          "_id": "65421d801c128f2ed899a7a4",
          "customer": "6540debf6ca6663eb886eb46",
          "service": "65421c961c128f2ed899a631",
          "__v": 0
        }
      ]
    }
  ]
}

报错的带Pipeline写法 #

{
  $lookup: {
    from: "reserves",
    let: { reserveData: "$reserve" },
    as: "reserve",
    pipeline: [
      {
        $match: {
          $expr: {
            $eq: [ "$_id", { $toObjectId: "$$reserveData" } ]
          }
        }
      }
    ]
  }
}

错误原因 #

常规$lookup会自动处理localField为数组的场景，将数组内的每个元素与foreignField做匹配；但带pipeline的$lookup需要显式处理变量类型：

• 原文档中reserve字段是数组类型（存储ObjectId字符串）
• $toObjectId仅支持单个字符串输入，直接传入数组会触发类型转换错误

解决方法 #

根据reserve字段的实际存储情况选择对应写法：

场景1：reserve为单元素数组（仅存一个ObjectId字符串） #

取数组第一个元素转换为ObjectId后匹配：

{
  $lookup: {
    from: "reserves",
    let: { reserveData: "$reserve" },
    as: "reserve",
    pipeline: [
      {
        $match: {
          $expr: {
            $eq: [ "$_id", { $toObjectId: { $first: "$$reserveData" } } ]
          }
        }
      }
    ]
  }
}

场景2：reserve为多元素数组（存多个ObjectId字符串） #

用$map遍历数组，将每个元素转换为ObjectId后，用$in匹配：

{
  $lookup: {
    from: "reserves",
    let: { reserveData: "$reserve" },
    as: "reserve",
    pipeline: [
      {
        $match: {
          $expr: {
            $in: [
              "$_id",
              { $map: { input: "$$reserveData", as: "id", in: { $toObjectId: "$$id" } } }
            ]
          }
        }
      }
    ]
  }
}

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