$ match to $ lookup result

Question

I have the following mango code:

db.users.aggregate([ { $match: { $and: [ { UserName: { $eq: 'administrator' } }, { 'Company.CompanyName': { $eq: 'test' } } ] } }, { $lookup: { from: "companies", localField: "CompanyID", foreignField: "CompanyID", as: "Company" } }, ]) 

Part of the $lookup code works fine. I got the following result:

But if I add the $match code to the code, it will bring nothing.

I found that the problem is the second match: { 'Company.CompanyName': { $eq: 'test' } } , but I can’t understand what is wrong with it. Any ideas?

UPDATE:

I also tried $unwind on the result of $lookup , but no luck:

 db.users.aggregate([ { $match: { $and: [ { UserName: { $eq: 'administrator' } }, { 'Company.CompanyName': { $eq: 'edt5' } } ] } }, { unwind: '$Company' }, { $lookup: { from: 'companies', localField: 'CompanyID', foreignField: 'CompanyID', as: 'Company' } }, ]) 

Answer 1

With MongoDB 3.4, you can run the aggregation pipeline, which uses the $addFields and $filter pipelines to return a Company array with only elements that match this condition. You can then wrap the $filter expression with $arrayElemAt to return a single document, which essentially includes $unwind , smoothing the array.

Follow this example to understand the concept above:

 db.users.aggregate([ { "$match": { "UserName": "administrator" } }, { "$lookup": { "from": 'companies', "localField": 'CompanyID', "foreignField": 'CompanyID', "as": 'Company' } }, { "$addFields": { "Company": { "$arrayElemAt": [ { "$filter": { "input": "$Company", "as": "comp", "cond": { "$eq": [ "$$comp.CompanyName", "edt5" ] } } }, 0 ] } } } ])