CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
Sun Lee
Sun Lee

Reputation: 939

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

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?

Upvotes: 32

Views: 22781

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 311865

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()

Upvotes: 63

Related Questions