基于多个匹配条件,从嵌套列表中返回子文档。

3

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

{
"_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"
    }

}   
])

@chridam Mongo版本:3.4,我已经在问题中包含了我尝试过的内容。 - utsav anand
1个回答

2
使用 $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" } }
])

它给我一个错误,无法识别管道阶段$replaceroot,但是在删除{“$replaceRoot”:{“newRoot”:“$messagelist”}}之后,它就像魔术一样工作了。非常感谢... :) - utsav anand

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接