My mongoDB statistical query is not performing as expected even after I aggregate all of the queries into a mongoDB aggregation. The final query result still contains an incorrect value.
[
{
"$match": {
"agencyId": ObjectId("649aedc8a827e7aed6ea7b79") // Replace with the actual agency ID
}
},
{
"$lookup": {
"from": "OperationTransfer", // Name of the transfer collection
"localField": "_id", // Field in operations referencing transfer document
"foreignField": "operationId", // Field in transfer collection
"as": "transfer"
}
},
{
"$unwind": {
"path": "$transfer",
"preserveNullAndEmptyArrays": true // Preserve operations without a transfer reference
}
},
{
"$group": {
"_id": "$status",
"count": { "$sum": 1 },
"countWithBabySeats": {
"$sum": {
"$cond": [{ "$eq": ["$babySeats", true] }, 1, 0]
}
},
"countInTruck": {
"$sum": {
"$cond": [{ "$eq": ["$inTruck", true] }, 1, 0]
}
},
"countTransferWithNonEmptyOriginDestiny": {
"$sum": {
"$cond": [
{
"$and": [
{ "$ne": ["$transfer", null] },
{ "$ne": ["$transfer.origin", ""] },
{ "$ne": ["$transfer.destiny", ""] }
]
},
1,
0
]
}
}
}
}
]
countTransferWithNonEmptyOriginDestiny in this stage, I can't check if i have the transfer object and if it contains the appropriate attribute with the right values
I used $set to change the stage to update the result documents, but the values were still incorrect.
if i change preserveNullAndEmptyArraysto false
It will partially work but it will messing the result of the others counts
Like @cmgchechess comment, I should check if the field exists instead of checking for null
"countTransferWithNonEmptyOriginDestiny": {
"$sum": {
"$cond": [
{
"$and": [
{
"$ne": [
{
$type: "$transfer"
},
"missing"
]
},
{
"$ne": [
"$transfer.origin",
""
]
},
{
"$ne": [
"$transfer.destiny",
""
]
}
]
},
1,
0
]
}
}