Using $match after a $lookup in MongoDB

Question

I have two collections and I want to get fields from both, so I'm using $lookup in an aggregation pipeline.

This works fine and returns all the documents with an extra field, an array with 0 or 1 elements (an object). If 0 elements, it means that the JOIN(in SQL world) didn't return anything. If 1 element, it means that there was a match and the element in an object with the fields of the second collection.

Now that I have those results, I'd like to use $match in order to filter some of the results.

In order to use $match I first want to use $unwind on that new extra field in order to extract the array. The problem is once I insert the $unwind stage, the result of the query is a single document.

Why is this happening? How can I $unwind and then $match all the documents I got from the $lookup stage?

Answer 1

assume we have documents after lookup:

{doc:{_id:1, lookupArray:[{doc:1},{doc:2}]}}

and

{doc:{_id:2, lookupArray:[/* empty */]}}

when we $unwind without any options we will get:

1. {doc:{_id:1, lookupArray:{doc:1}}}
2. {doc:{_id:1, lookupArray:{doc:2}}}
3. null

and when we specify

 { $unwind: { path: "$array", preserveNullAndEmptyArrays: true } }

then we will get:

1. {doc:{_id:1, lookupArray:{doc:1}}}
2. {doc:{_id:1, lookupArray:{doc:2}}}
3. {doc:{_id:2, lookupArray:[/* empty */]}}

So when you want to perform a search for a value doc from lookupArray, $match will look like this:

   {$match:{'lookupArray.doc':2}}

Any comments welcome!