mongodb - 根据多个匹配条件从嵌套列表中返回子文档

Question

您好，我有以下文档，我想根据多个条件从消息列表中提取消息。

{
"_id" : ObjectId("58df770371043e7087cdaadd"),
"prospectemailid" : "abc@gmail.com",
"prospectid" : "1491038545032",
"useremail" : "xyz@gmail.com",
"threadidslist" : [ 
    {
        "threadid" : "15b28e8e711f71b0",
        "subject" : "sub",
        "campaignid" : "1491460056589",
        "messagelist" : [ 
            {
                "emailuniqueid" : "1492376430400",
                "messageid" : "15b28e8e711f71b0",
                "timestamp" : "Sat Apr 01 15:16:43 IST 2017",
                "from" : "utsavanand.work@gmail.com",
                "body" : "Hello",
                "labelid" : "SENT",
                "to" : "anuragkv10@gmail.com",
                "messageidpayload" : ""
            }, 
            {
                "emailuniqueid" : "1492376430400",
                "messageid" : "15b28ecbcbe5b32d",
                "timestamp" : "Sat Apr 01 15:20:54 IST 2017",
                "from" : "anuragkv10@gmail.com",
                "body" : "Hi",
                "labelid" : "RECEIVED",
                "to" : "utsavanand.work@gmail.com",
                "messageidpayload" : "<CAL_CU77Rc27peuGde=WTC7waW3gfvS2Wr_t2A+7KBjjxsKW8Sw@mail.gmail.com>"
            }
        ]
    }
]

预期输出基于标准:prospectemailid=1491038545032,useremail=xyz@gmail.com,threadidslist.campaignid=1491460056589 和 messagelist.emailuniqueid=1492376430400 是

{
               "emailuniqueid" : "1492376430400",
                "messageid" : "15b28ecbcbe5b32d",
                "timestamp" : "Sat Apr 01 15:20:54 IST 2017",
                "from" : "sad@gmail.com",
                "body" : "Hi",
                "labelid" : "RECEIVED",
                "to" : asd@gmail.com",
                "messageidpayload" : "<CAL_CU77Rc27peuGde=WTC7waW3gfvS2Wr_t2A+7KBjjxsKW8Sw@mail.gmail.com>"
 }

谢谢...!

到目前为止我已经尝试过:

db.getCollection('GD').aggregate(
[

   { $match:{
        "prospectid" : "1491038545032",
        "useremail" : "xyz@gmail.com",
        "threadidslist.campaignid" : "1491460056589",
        "threadidslist.messagelist.emailuniqueid" : "1492376430400"
    }

}   
])

Answer 1

使用 $arrayElemAt 和 $filter 运算符在 $project 管道获取过滤后的嵌套数组。 <强> $replaceRoot 管道会将过滤后的子文档提升到顶层并替换所有其他字段。

考虑运行以下管道以获得所需的结果:

db.GD.aggregate([
    { "$match": {
        "prospectid" : "1491038545032",
        "useremail" : "xyz@gmail.com",
        "threadidslist.campaignid" : "1491460056589",
        "threadidslist.messagelist.emailuniqueid" : "1492376430400"
    } }, 
    { "$project": { 
        "threadidslist": {
            "$arrayElemAt": [
                { 
                    "$filter": {
                        "input": "$threadidslist",
                        "as": "thread",
                        "cond": { "$eq": ["$$thread.campaignid", "1491460056589"] }
                    }                
                },
                0
            ]
        }
    } },
    { "$project": { 
        "messagelist": {
            "$arrayElemAt": [
                { 
                    "$filter": {
                        "input": "$threadidslist.messagelist",
                        "as": "msg",
                        "cond": { "$eq": ["$$msg.emailuniqueid", "1492376430400"] }
                    }                
                },
                0
            ]
        }
    } },
    { "$replaceRoot": { "newRoot": "$messagelist" } }
])