I was searching for one aggregation query which can provide output as per below condition.
db.Collection.aggregate(
[{$match: {$and : [
{"_createdAt": { $gte : new ISODate('2021-05-01T00:00:00.000Z')}},{"_createdAt": { $lt : new ISODate('2022-02-02T00:00:00.000Z')}},
{"ApiRequestType" : "InstrumentDetails"},{"RequesterSystem" : "Generic_Reporting"}]}},
{$group:
{_id:{day: { $dayOfMonth: "$_createdAt" },month: { $month: "$_createdAt" },year: { $year: "$_createdAt" }},count: { $sum:1 },
date: { $first: "$_createdAt" }}},
{$project:{date:{$dateToString: { format: "%Y-%m-%d", date: "$date" }},count: 1,_id: 1,
numberOfRMCount: { $cond: { if: { $isArray: "$ResponseMessage" }, then: { $size: "$ResponseMessage" }, else: "NA"}}},
}])
But I am not getting count as per daily empty ResponseMessage field. it giving only NA Pls help on this.
I am getting below output,
count date numberOfRMCount
49 05-05-2021 NA
35 04-05-2021 NA
Require output as per below,
count date numberOfRMCount
49 05-05-2021 number of empty ResponseMessage fields
35 04-05-2021 number of empty ResponseMessage fields
What should I change in Query.
You need "ResponseMessage" in $group. I used $push and I'm pretty sure that means "ResponseMessage" will always be an array so checking to see if it is would be redundant.
db.Collection.aggregate([
{ $match: {
$and: [
{ "_createdAt": { $gte: ISODate("2021-05-01T00:00:00.000Z") } },
{ "_createdAt": { $lt: ISODate("2022-02-02T00:00:00.000Z") } },
{ "ApiRequestType": "InstrumentDetails" },
{ "RequesterSystem": "Generic_Reporting" }
]
}
},
{ $group: {
_id: {
day: { $dayOfMonth: "$_createdAt" },
month: { $month: "$_createdAt" },
year: { $year: "$_createdAt" }
},
count: { $sum: 1 },
date: { $first: "$_createdAt" },
"ResponseMessage": { "$push": "$ResponseMessage" }
}
},
{ $project: {
date: {
$dateToString: {
format: "%Y-%m-%d",
date: "$date"
}
},
count: 1,
_id: 1,
numberOfRMCount: { $size: "$ResponseMessage" }
}
}
])
Try it on mongoplayground.net