$lookup with pipeline match and projection does not work for guid

Question

I have two collections that I want to join with $lookup based on two id fields. Both fields are from type guid and looke like this in mongodb compass: 'Binary('cavTZa/U2kqfHtf08sI+Fg==', 3)'

This syntax in the compass aggregation pipeline builder gives the expected result:

{
  from: 'clients',
  localField: 'ClientId',
  foreignField: '_id',
  as: 'ClientData'
}

But i want to add some projection and tried to change it like this:

{
  from: 'clients',
  'let': {
    id: '$_id.clients'
  },
  pipeline: [
    {
      $match: {
        $expr: {
          $eq: [
            '$ClientId',
            '$$id'
          ]
        }
      }
    },
    {
      $project: {
        Name: 1,
        _id: 0
      }
    }
  ],
  as: 'ClientData'
}

But the result here is that every client from collection 'clients' is added to every document in the starting table. I have to use MongoDB 3.6 so the new lookup syntax from >=5.0 is not available.

Any ideas for me? Does $eq work for binary stored guid data?

Answer 1

In the first example, you say that the local field is ClientId and the foreign field is _id. But that's not what you used in your second example.

This should work better:

{
  from: 'clients',
  'let': {
    ClientId: '$ClientId'
  },
  pipeline: [
    {
      $match: {
        $expr: {
          $eq: [
            '$$ClientId',
            '$_id'
          ]
        }
      }
    },
    {
      $project: {
        Name: 1,
        _id: 0
      }
    }
  ],
  as: 'ClientData'
}