Return a supporting document from a nested list based on multiple matching criteria
Hi I have the following document, I want to extract a message from a messagelist based on multiple criteria.
{
"_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>"
}
]
}
]
}
Expected result based on Criteria: prostemailid = 1491038545032, useremail = xyz @ gmail.com, threadidslist.campaignid = 1491460056589 and 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>"
}
Thank!..
So far I have tried:
db.getCollection('GD').aggregate(
[
{ $match:{
"prospectid" : "1491038545032",
"useremail" : "xyz@gmail.com",
"threadidslist.campaignid" : "1491460056589",
"threadidslist.messagelist.emailuniqueid" : "1492376430400"
}
}
])
+3
source to share
1 answer
Use and > t21> in the pipeline to get a filtered nested array. The conveyor will advance the filtered subdocument to the top level and replace all other fields. $arrayElemAt
$project
$replaceRoot
Consider the following pipeline to get the desired result:
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" } }
])
+2
source to share