0

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?

M. Altmann
  • 726
  • 5
  • 20

1 Answers1

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'
}
Adrien D
  • 36
  • 2
  • Thank you - I did not get the order right. But your sample needs two changes: In the let 'ClientId' is not a valid variable name and in the '$eq' the variables have to be switched. – M. Altmann Apr 06 '22 at 07:08