如果Mongo $ lookup是左外部联接，那么它将如何排除不匹配的文档呢? [英] 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?

我正在测试Mongo 3.2中的新聚合器，到目前为止，我通过首先展开，然后将文档分组来执行嵌套数组查找.我剩下的就是要让结果包含所有不符合$lookup标准的本地文档，这就是我认为的左外部联接"的标准定义.

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".

以下是查询:

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

因此，如果我有一个user，其中有一个空的profile.universities数组，那么无论$lookup返回任何匹配项，我都需要将其包含在结果集中，但事实并非如此.我该怎么做，以及Mongo为什么构造$lookup以此方式运行的任何原因?

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?

推荐答案

此行为与$lookup无关，这是因为

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.

要保留展开的文档，即使profile.universities为空数组，也可以将其preserveNullAndEmptyArrays选项设置为true:

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

这篇关于如果Mongo $ lookup是左外部联接，那么它将如何排除不匹配的文档呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！