MongoEngine聚合$lookup关联_id失败问题求助

尝试将Campaign记录与Booking记录通过Campaign._id和Booking.campaignId关联，但使用$lookup后attaches字段始终为空。

Campaign记录 #

{
  "_id": {
    "$oid": "64eb81337f7d9f6e1107fc3a"
  },
  "_cls": "CampaignModel",
  "campaignNumber": "CMPN-00005",
  "account": {
    "$oid": "64d80694fe75052b39c0e615"
  },
  "campaignName": "title",
  "campaignDescription": "wdesc",
  "campaignStartDate": {
    "$date": "2023-08-17T00:00:00.000Z"
  },
  "campaignEndDate": {
    "$date": "2023-08-27T00:00:00.000Z"
  },
  "isWorkOrderGeneratedFlag": false,
  "isInvoiceGenerated": false,
  "campaignCost": "20000",
  "clientName": "",
  "attachedBillboardList": [],
  "accountId": "64d80694fe75052b39c0e615"
}

Booking记录 #

{
  "_id": {
    "$oid": "64eb81337f7d9f6e1107fc3b"
  },
  "_cls": "BookingModel",
  "campaignId": "64eb81337f7d9f6e1107fc3a",
  "billboardId": "64e0d18aaec5d7ef7bf848dd",
  "startDate": {
    "$date": "2023-09-01T00:00:00.000Z"
  },
  "endDate": {
    "$date": "2023-09-10T00:00:00.000Z"
  },
  "costPerDay": 200,
  "billboard": {
    "_ref": {
      "$ref": "BillBoard",
      "$id": {
        "$oid": "64e0d18aaec5d7ef7bf848dd"
      }
    },
    "_cls": "BillBoardModel"
  }
}

MongoEngine模型类 #

CampaignModel #

class CampaignModel(BaseModel):
    meta = {'collection': 'Campaign'}
    campaignNumber = me.SequenceField(sequence_name="CAMPAIGN",  value_decorator=generate_campaign_number)
    account = me.ReferenceField(UserModel)
    campaignName = me.StringField()
    campaignDescription = me.StringField()
    campaignStartDate = me.DateField(default=datetime.datetime.now)
    campaignEndDate = me.DateField(default=datetime.datetime.now)
    isWorkOrderGeneratedFlag = me.BooleanField(default=False)
    isInvoiceGenerated = me.BooleanField(default=False)
    campaignCost = me.StringField()
    clientName = me.StringField()
    attachedBillboardList = me.ListField()

BookingModel #

class BookingModel(BaseModel):
    meta = {'collection': 'Booking'}
    campaignId = me.StringField()
    campaign = me.LazyReferenceField(CampaignModel,dbref=False)
    billboardId = me.StringField()
    accountId = me.StringField()
    billBoard = me.LazyReferenceField(BillBoardModel,dbref=False)
    startDate = me.DateField()
    endDate = me.DateField()
    costPerDay = me.FloatField()

当前查询语句 #

records = CampaignModel.objects.aggregate(*[
            {
                "$lookup": {
                    "from": "Booking",
                    "localField": "_id",
                    "foreignField": "campaignId",
                    "as": "attaches"
                }

            }])

问题现象 #

• 预期：Campaign记录中包含attaches数组，存放对应的Booking记录
• 实际：attaches字段始终为空

解决方案 #

核心原因 #

MongoDB的$lookup严格匹配字段类型：Campaign的_id是ObjectId类型，而Booking的campaignId是字符串类型，即使值的字符串形式相同，类型不匹配也会导致关联失败。

方法一：统一字段类型（推荐） #

修改BookingModel的campaignId字段类型为ObjectIdField，确保存储类型与Campaign的_id一致：

class BookingModel(BaseModel):
    meta = {'collection': 'Booking'}
    campaignId = me.ObjectIdField()  # 将StringField改为ObjectIdField
    campaign = me.LazyReferenceField(CampaignModel,dbref=False)
    billboardId = me.StringField()
    accountId = me.StringField()
    billBoard = me.LazyReferenceField(BillBoardModel,dbref=False)
    startDate = me.DateField()
    endDate = me.DateField()
    costPerDay = me.FloatField()

修改后需要重新保存所有Booking数据，确保campaignId存储为ObjectId而非字符串，之后原查询语句即可正常工作。

方法二：聚合时转换类型（无需修改模型） #

在聚合管道中先将Campaign的_id转换为字符串，再与Booking的campaignId关联：

records = CampaignModel.objects.aggregate(*[
    {
        "$addFields": {
            "id_str": {"$toString": "$_id"}  # 将ObjectId转为字符串
        }
    },
    {
        "$lookup": {
            "from": "Booking",
            "localField": "id_str",  # 使用转换后的字符串字段关联
            "foreignField": "campaignId",
            "as": "attaches"
        }
    },
    {
        "$project": {
            "id_str": 0  # 可选，移除临时添加的id_str字段
        }
    }
])

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