$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?

Adrien D · Accepted Answer · 2022-04-05 16:04:13Z

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' }

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related