mongoengine aggregate lookup on _id not working

Campaign Record

{
  "_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 Record

{
  "_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"
  }
}

I am trying to join the campaign with booking on _id from the campaign record and campaignId in the booking record.

Campaign Mongoengine class

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()

Booking Mongoengine Class

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()

query

records = CampaignModel.objects.aggregate(*[
            {
                "$lookup": {
                    "from": "Booking",  # Tag collection database name
                    "localField": "_id",  # Reference field
                    "foreignField": "campaignId",  # Primary key of the Tag collection
                    "as": "attaches"
                }

            }])

Expected: Records from campaign collection with records from Booking collection as an array in attaches key.

But the attaches key was empty.

The problem was you were storing the campaignId as a string type in the Booking collection. Make sure that both values to be compared must be same type before comparing. Thus, you need to convert the campaignId for the document in the Booking collection as ObjectId type as below:

db.Campaign.aggregate([
  {
    "$lookup": {
      "from": "Booking",
      let: {
        campaignId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$campaignId",
                {
                  $toObjectId: "$campaignId"
                }
              ]
            }
          }
        }
      ],
      "as": "attaches"
    }
  }
])

Demo @ Mongo Playground