If Mongo $lookup is a left outer join, then how come it excludes non-matching documents?

Question

The title says it all. How come if a document does not result in any matching outer document according to its matching field, then how come it's not included in the pipeline's result set?

I'm testing out the new aggregators in Mongo 3.2 and I've gone so far as to perform a nested array lookup by first unwinding, and then grouping the documents back up. All I have left is to have the results include all local documents that didn't meet the $lookup criteria, which is what I thought was the standard definition of "left outer join".

Here's the query:

db.users.aggregate([
    {
        $unwind: "$profile",
        $unwind: "$profile.universities"
    },
    {
        $lookup: {
            from: "universities",
            localField: "profile.universities._id",
            foreignField: "_id",
            as: "profile.universities"
        }
    },
    {
        $group: {
            _id: "$_id",
            universities: {
                $addToSet: "$profile.universities"
            }
        }
    }
]).pretty()

So if I have a user that has an empty profile.universities array, then I need it to be included in the result set regardless of the $lookup returning any matches, but it does not. How can I do this, and any reason why Mongo constructed $lookup to operate this way?

Solution

This behavior isn't related to $lookup, it's because the default behavior for $unwind is to omit documents where the referenced field is missing or an empty array.

To preserve the unwound documents even when profile.universities is an empty array, you can set its preserveNullAndEmptyArrays option to true:

db.users.aggregate([
    {
        $unwind: "$profile",
        $unwind: {
            path: "$profile.universities",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup: {
            from: "universities",
            localField: "profile.universities._id",
            foreignField: "_id",
            as: "profile.universities"
        }
    },
    {
        $group: {
            _id: "$_id",
            universities: {
                $addToSet: "$profile.universities"
            }
        }
    }
]).pretty()

Comments

[Dmytro Grynets]

Thanks so much, this is the weirdest behavior I have ever seen, fought this bug for like a week, if it wasn't for your answer I would never try to see in that direction

[user3166111]

How to write the above in c# ?