mongodb - 过滤来自多个集合的输出

Question

假设我们有一个这样的脚本数组：

 ["xxxxx1","xxxxx2","xxxxx3","xxxxx4"] 

我们在 db 中存在以下脚本集合：

Executed:{"_id" : ObjectId("xxx"),"scrip" : "xxxxx1" },{"_id" : ObjectId("xxy"),"scrip" : "xxxxx3" }......
In-process:{"_id" : ObjectId("xxx"),"scrip" : "xxxxx4" }, ....
Rejected:{"_id" : ObjectId("xxx"),"scrip" : "xxxxx5" }....

我们希望得到上述任何集合中都不存在的脚本数组。预期输出：

["xxxxx2"]

这如何在单个 mongodb 管道/查询中完成？

标签： mongodbmongodb-queryaggregation-frameworkmongodb-java

Answer 1

以下查询可以为我们提供预期的输出：

db.executed.aggregate([
    {
        $group:{
            "_id":null,
            "executedScrips":{
                $addToSet:"$scrip"
            }
        }
    },
    {
        $lookup:{
            "from":"inprocess",
            "pipeline":[
                {
                    $group:{
                        "_id":null,
                        "inprocessScrips":{
                            $addToSet:"$scrip"
                        }
                    }
                }
            ],
            "as":"inprocessLookup"
        }
    },
    {
        $lookup:{
            "from":"rejected",
            "pipeline":[
                {
                    $group:{
                        "_id":null,
                        "rejectedScrips":{
                            $addToSet:"$scrip"
                        }
                    }
                }
            ],
            "as":"rejectedLookup"
        }
    },
    {
        $unwind:{
            "path":"$inprocessLookup",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $unwind:{
            "path":"$rejectedLookup",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $project:{  
            "scrips":{
                $concatArrays:[
                    "$executedScrips", 
                    {
                        $ifNull:["$inprocessLookup.inprocessScrips",[]]
                    },
                    {
                        $ifNull:["$rejectedLookup.rejectedScrips",[]]
                    }
                ]
            }
        }
    },
    {
        $project:{
            "_id":0,
            "notFound":{
                $setDifference:[["xxxxx1","xxxxx2","xxxxx3","xxxxx4"],"$scrips"]
            }
        }
    }
]).pretty()

数据集：

收藏：executed

    { "_id" : ObjectId("5d60e572f00e0c8c3593b5ff"), "scrip" : "xxxxx1" }
    { "_id" : ObjectId("5d60e572f00e0c8c3593b600"), "scrip" : "xxxxx3" }

收藏：inprocess

    { "_id" : ObjectId("5d60f23ff00e0c8c3593b601"), "scrip" : "xxxxx4" }

收藏：rejected

    { "_id" : ObjectId("5d60f260f00e0c8c3593b602"), "scrip" : "xxxxx5" }

输出：

{ "notFound" : [ "xxxxx2" ] }

注意：如果集合中没有记录，则查询将失败，executed因为聚合从那里开始。

---

更新 I：将传递请求数组而不是脚本数组

以下查询可以为我们提供预期的输出：

db.executed.aggregate([
    {
        $group:{
            "_id":null,
            "executedScrips":{
                $addToSet:"$scrip"
            }
        }
    },
    {
        $lookup:{
            "from":"inprocess",
            "pipeline":[
                {
                    $group:{
                        "_id":null,
                        "inprocessScrips":{
                            $addToSet:"$scrip"
                        }
                    }
                }
            ],
            "as":"inprocessLookup"
        }
    },
    {
        $lookup:{
            "from":"rejected",
            "pipeline":[
                {
                    $group:{
                        "_id":null,
                        "rejectedScrips":{
                            $addToSet:"$scrip"
                        }
                    }
                }
            ],
            "as":"rejectedLookup"
        }
    },
    {
        $unwind:{
            "path":"$inprocessLookup",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $unwind:{
            "path":"$rejectedLookup",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $project:{  
            "scrips":{
                $concatArrays:[
                    "$executedScrips", 
                    {
                        $ifNull:["$inprocessLookup.inprocessScrips",[]]
                    },
                    {
                        $ifNull:["$rejectedLookup.rejectedScrips",[]]
                    }
                ]
            }
        }
    },
    {
        $addFields:{
            "requests":[
                {
                    "requestid" : "R1", 
                    "stocks" : ["xxxxx1","xxxxx2","xxxxx3","xxxxx4"]
                },
                {
                    "requestid" : "R2", 
                    "stocks" : ["xxxxx1","xxxxx3","xxxxx4"]
                },
                {
                    "requestid" : "R3", 
                    "stocks" : ["xxxxx1","xxxxx3","xxxxx4","xxxxx10"]
                }
            ]
        }
    },
    {
        $project:{
            "_id":0,
            "unmatchedRequests":{
               $map:{
                    "input":"$requests",
                    "as":"request",
                    "in":{
                        $concat:{
                            $cond:[
                                {
                                    $gt:[
                                        {
                                            $size:{
                                                $setDifference:["$$request.stocks","$scrips"]
                                            }
                                        },
                                        0
                                    ]
                                },
                                "$$request.requestid",
                                null
                            ]
                        }
                    }
               }
            }
        }
    },
    {
        $project:{
            "unmatchedRequests":{
                $filter:{
                    "input":"$unmatchedRequests",
                    "as":"unmatchedRequest",
                    "cond":{
                        $ne:["$$unmatchedRequest",null]
                    }
                }
            }
        }
    }
]).pretty()

输出：

{ "unmatchedRequests" : [ "R1", "R3" ] }

注意：在第 7 个聚合阶段，我们正在注入请求数组。