mongodb - 聚合子查询，在特定_id的列表中搜索

Question

在很多讲座之后，我不能说是否可以在 1 查询 中使用 mongo 执行这个 sql 等效查询:
SELECT * from collection WHERE _id NOT IN (SELECT blacklist from集合 WHERE _id = 1 )我尝试了很多关于聚合的事情，但没有成功。

这是我的收藏:

{
    "_id" : 1,
    "blacklist" : [8,9,10,3]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.72, 
            48.91
        ]
    }
}

{
    "_id" : 2,
 "blacklist" : [18,1,93]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.63, 
            48.91
        ]
    }
}

{
    "_id" : 3,
     "blacklist" : [7,3]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.7, 
            48.96
        ]
    }
}

此查询的预期结果和此集合应为(排除 _id 3，因为在 _id 1 的黑名单中):

{
        "_id" : 1,
        "blacklist" : [8,9,10,3]
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.72, 
                48.91
            ]
        }
    }

    {
            "_id" : 2,
         "blacklist" : [18,1,93]
            "code_postal" : 67110,
            "loc" : {
                "type" : "Point",
                "coordinates" : [ 
                    7.63, 
                    48.91
                ]
            }
        }

问候

Answer 1

我认为你应该省去麻烦，只使用两个查询(首先，获取黑名单，然后查询文档)但如果真的没有其他方法:

db.so.aggregate([
{
    // First, choose what fields to return (less is better)
    // and only return a certain document {_id: 1}'s blacklist.
    $project: {
        _id:         1,
        code_postal: 1,
        loc:         1,
        bl:          {
            // Only return blacklists if the parent has 
            // a certain ID.
            $cond: {
                if:   {$eq: ["$_id", 1]}, // or a different value
                then: "$blacklist",
                else: 0
            }
        }
    }
},

{
    // Group all documents in one, so that we can embed the
    // blacklist into all documents, not just in {_id:1}.
    $group: {
        _id:       null, // Group everything.
        items:     { $push: "$$ROOT" },
        blacklist: { $max: "$bl" } // "{}" is higher than "0".
                                   // This makes sure that we only
                                   // get one blacklist, not
                                   // [ [], 0, 0, 0, ... ]
    }
},

{
    // Pull the documents apart again.
    $unwind: "$items"
},

{
    $project: {
        _id:         "$items._id",
        code_postal: "$items.code_postal",
        loc:         "$items.loc",
        whitelisted: {
            // If everything in the following array is true,
            // then the _id is not in the blacklist.
            $allElementsTrue: [{
                $map: {
                    // Iterate over $blacklist
                    input: "$blacklist",
                    as:    "currentId",
                    in:    {
                        // If the ids don't match, return true.
                        $ne: ["$$currentId", "$items._id"]
                    }
                }
            }]
        }
    }
},

{
    // Only find non-blacklisted documents.
    $match: {
        "whitelisted": true
    }
}

]);

请注意，由于这会将所有文档归为一个，因此您必须注意不要超过 Mongo's document size limits .

这会产生以下内容:

[ 
    {
        "_id" : 1,
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.72, 
                48.91
            ]
        },
        "whitelisted" : true
    }, 
    {
        "_id" : 2,
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.63, 
                48.91
            ]
        },
        "whitelisted" : true
    }
]