I have a collection with data given below
{
"_id" : ObjectId("5fc8a58257f6526c6a520725"),
"job_id" : ObjectId("5fc8a5822aed5820240c2893"),
"access_id" : ObjectId("5eda0b2495b51c1d746db8b2"),
"campaign_num" : "488707373",
"partner_id" : ObjectId("5a94e02bb0445b1cc742d735"),
"parent_partner" : ObjectId("5a8d70c4b0445b6a883fd395"),
"status" : "A",
"route" : "SR",
"otp" : true,
"trans" : false,
"job_payload" : {
"senderid" : "36",
"msisdn" : "9671660999",
"script" : "Your verification code is : 166297",
"pingbackurl" : "https://xyztesting.com/sms/v1/notify/pr/index.php",
"templateid" : "769",
"smstype" : "2"
},
"track" : {
"added" : {
"date" : ISODate("2020-12-03T08:44:50.000Z")
},
"routed" : ISODate("2020-12-03T08:44:50.000Z")
},
"message_parts" : 1,
"provider_status" : {
"job_id" : "",
"count" : {
"total" : 1,
"sent" : 0,
"delivered" : 0,
"failed" : 0
},
"delivery" : []
}
}
I wanted to count all the totals,sent,delivered,failed where campaign_num and date were same . ie using the same campaign_num and on the same date. I created the view using below query
var pipeline = [
{
$group: {
_id: {
campaign: "$campaign_num",
date: { $dateToString: { format: "%Y-%d-%m", date: "$track.added.date" } }
},
total: { $sum: "$provider_status.count.total" },
sent: { $sum: "$provider_status.count.sent" },
delivered: { $sum: "$provider_status.count.delivered" },
failed: { $sum: "$provider_status.count.failed" }
}
}
]
db.createView("sms_daily_test","col_outbox",pipeline)
I am getting the output as shown below
"_id" : {
"campaign" : "488707373",
"date" : "2021-19-01"
},
"total" : 3,
"sent" : 3,
"delivered" : 0,
"failed" : 0
}
Now the main problem is that I want to include partner_id and access_id fields also but not inside the _id. ie I want output to be something like this
"_id" : {
"campaign" : "488707373",
"date" : "2021-19-01"
},
"total" : 3,
"sent" : 3,
"delivered" : 0,
"failed" : 0,
"partner_id":ObjectId("5a94e02bb0445b1cc742d735"),
"access_id":ObjectId("5a94e02bb0445b1cc742d635")
}
I am using the below code for the purpose
var pipeline = [
{
$group: {
_id: {
campaign: "$campaign_num",
date: { $dateToString: { format: "%Y-%d-%m", date: "$track.added.date" } }
},
partner_id: "$partner_id",
access_id: "$access_id",
total: { $sum: "$provider_status.count.total" },
sent: { $sum: "$provider_status.count.sent" },
delivered: { $sum: "$provider_status.count.delivered" },
failed: { $sum: "$provider_status.count.failed" }
}
}
]
db.createView("sms_daily_testing2","col_outbox",pipeline)
When I run it I get the following error message:
The field 'partner_id' must be an accumulator object
.
The information about this field is that a particular campaign will have only one partner_id
and one access_id
. Although a partner_id
can have multiple campaigns and a access_id
can also have multiple campaigns.
I need to group the data by campaigns and date field which I have done. I just need to know how to add these two fields without puting it in the inside the _id
field.
You have written that a given campaign and date will have a unique partner_id and access_id. And we must use accumulator object outside of _id field. Since we have only one partner and access_id we can use $first or $last as shown :
var pipeline=[{$group:{_id:{campaign:"$campaign_num",date:{$dateToString:{format:"%Y-%m-%d",date:"$track.added.date"}}},
partner_id:{$first:"$partner_id"},
access_id:{$first:"$access_id"},
total: {$sum: "$provider_status.count.total"},
sent:{$sum:"$provider_status.count.sent"},
delivered:{$sum:"$provider_status.count.delivered"},
failed:{$sum:"$provider_status.count.failed"}}
}]