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

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

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)