Referencing root _id in aggregate lookup match expression not working

Question

This is my first experience using aggregate pipeline. I'm not able to get a "$match" expression to work inside the pipeline. If I remove the "_id" match, I get every document in the collection past the start date, but once I add the $eq expression, it returns empty.

I read a lot of other examples and tried many different ways, and this seems like it is correct. But the result is empty.

Any suggestions?

let now = new Date()

let doc = await Team.aggregate([
    { $match: { created_by: mongoose.Types.ObjectId(req.params.user_oid)} },
    { $sort: { create_date: 1 } },
    { $lookup: { 
        from: 'events', 
        let: { "team_oid": "$team_oid" },
        pipeline: [
          { $addFields: { "team_oid" : { "$toObjectId": "$team_oid" }}},
          { $match: {
              $expr: { 
                $and: [
                  { $gt: [ "$start", now ] },
                  { $eq: [ "$_id", "$$team_oid" ] }
                ]
              },
            } 
          }, 
          {
            $sort: { start: 1 }
          },
          {
            $limit: 1
          }
        ],
        as: 'events',
    }},
    {
        $group: {
            _id: "$_id",
            team_name: { $first: "$team_name" },
            status: { $first: "$status" },
            invited: { $first: "$invited" },
            uninvited: { $first: "$uninvited" },
            events: { $first: "$events.action" }, 
            dates: { $first: "$events.start" } ,
            team_oid: { $first: "$events.team_oid" }
        }
    }])

Example Docs (added by request)

Events:

_id:ObjectId("60350837c57b3a15a414d265")
invitees:null
accepted:null
sequence:7
team_oid:ObjectId("60350837c57b3a15a414d263")
type:"Calendar Invite"
action:"Huddle"
status:"Questions Issued"
title:"Huddle"
body:"This is a Huddle; you should receive new questions 5 days befor..."
creator_oid:ObjectId("5ff9e50a206b1924dccd691e")
start:2021-02-26T07:00:59.999+00:00
end:2021-02-26T07:30:59.999+00:00
__v:0

Team:

_id:ObjectId("60350837c57b3a15a414d263")
weekly_schedule:1
status:"Live"
huddle_number:2
reminders:2
active:true
created_by:ObjectId("5ff9e50a206b1924dccd691e")
team_name:"tESTI"
create_date:2021-02-23T13:50:47.172+00:00
__v:0

Answer 1

This is just a guess since you don't have schema in your question. But it looks like your have some of your _ids mixed up. Where you are currently trying to $match events whose _id is equal to a team_oid. Rather than the event's team_oid field being equal to the current 'team' _id.

I'm pretty confident this will produce the correct output. If you post any schema or sample docs I will edit it.

https://mongoplayground.net/p/5i1w2Ii7KCR

let now = new Date()

let doc = await Team.aggregate([
  { $match: { created_by: mongoose.Types.ObjectId(req.params.user_oid)} },
  { $sort: { create_date: 1 } },
  { $lookup: { 
      from: 'events', 
      // Set tea_oid as the current team _id 
      let: { "team_oid": "$_id" },
      pipeline: [
        { $match: {
            $expr: { 
              $and: [
                { $gt: [ "$start", now ] },
                // Match events whose 'team_oid' field matches the 'team' _id set above
                { $eq: [ "$team_oid", "$$team_oid" ] }
              ]
            },
          } 
        }, 
        {
          $sort: { start: 1 }
        },
        {
          $limit: 1
        }
      ],
      as: 'events',
  }},
  {
      $group: {
          _id: "$_id",
          team_name: { $first: "$team_name" },
          status: { $first: "$status" },
          invited: { $first: "$invited" },
          uninvited: { $first: "$uninvited" },
          events: { $first: "$events.action" }, 
          dates: { $first: "$events.start" } ,
          team_oid: { $first: "$events.team_oid" }
      }
  }])