I'm trying to build an aggregate query from a users collection. Each user has an id property that is associated in a clients collection array:

// users...
[
  {
    id: '12345',
    name: 'John'
  }
  // ...
}

// clients...
[
  {
    name: 'Foo',
    members: [ { id: '1234', role: 'Admin' } ]
  },
  // ....
]

So what I'm trying to do is aggregate the users collection and do a $lookup to "join" the clients with which a user is a member (by the id)

db.users.aggregate([
  $lookup: {
      from: 'clients',
      as: 'clients',
      let: { user_id: '$id' },
      pipeline: [
        {
          $match: {
            members: {
              $elemMatch: { id: '$user_id' },
            },
          },
        },
      ],
    };
}])

If I hard-code any user's id into the $elemMatch (replacing $user_id) it works, but I can't seem to get it to work as a variable from the user records.

CodePudding user response：

From $lookup let,

A $match stage requires the use of an $expr operator to access the variables. The $expr operator allows the use of aggregation expressions inside of the $match syntax.

From your scenario,

1. Need $expr to access the variable.
2. Apply the $in operator instead of $elemMatch.
3. To reference the variable in the pipeline, use $$<variable> but not $<variable>.

{
  $match: {
    $expr: {
      $in: [
        "$$user_id",
        "$members.id"
      ]
    }
  }
}

Sample Mongo Playground