在另一个集合中查找带有值的键

Question

我们有一个请求集合，示例文档如下所示：

{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ4",
    "scrip" : "5647"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ4",
    "scrip" : "5648"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : "0001"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : "0456"
}

纸片托收：

{
    "_id" : ObjectId("xxxx"),
    "scrip" : "0001"
}
{
    "_id" : ObjectId("xxxx"),
    "scrip" : "0456"
}
{
    "_id" : ObjectId("xxxx"),
    "scrip" : "5647"
}

我们需要返回的请求，其中有所有对应的凭证在纸片集合。

预期输出:

{"_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : ["0001","0456"]
}

我们如何做到这一点？

Answer 1

试试这个：

db.requests.aggregate([
    /** Filter to reduce dataset */
    { $match: { requestId: { $in: ['REQ4', 'REQ1', 'REQ5'] } } },
    /** check for matching docs based on scrip */
    {
        $lookup:
        {
            from: "scrips",
            localField: "scrip",
            foreignField: "scrip",
            as: "scrips"
        }
    }, { $addFields: { scrips: { $arrayElemAt: ['$scrips', 0] } } },
    /** Group all docs in request collection based on requestId */
    { $group: { _id: '$requestId', scrip: { $push: '$scrip' }, scrips: { $push: '$scrips' } } },
    /** Remove request docs if all request docs doesn't have match in scrips */
    { $match: { $expr: { $eq: [{ $size: '$scrip' }, { $size: '$scrips' }] } } },
    /** Transform final result */
    { $project: { _id: 0, requestId: '$_id', scrip: 1 } }
])

测试: MongoDB-游乐场