I am trying to run following query on RoboMongo Shell
From RoboMongo
db.getCollection('works').aggregate([{
"$match": {
"$and": [{
"employee": ObjectId("5c932e67d7a3cc2530919f75")
},
{
"$or": [{
"$and": [{
"start": {
"$lte": ISODate("2019-01-01T18:29:59.999Z")
}
},
{
"end": {
"$gte": ISODate("2019-01-01T18:29:59.999Z")
}
}
]
},
{
"$and": [{
"end": {
"$gte": ISODate("2019-01-14T18:30:00.000Z")
}
},
{
"start": {
"$lte": ISODate("2019-01-14T18:30:00.000Z")
}
}
]
}
]
}
]
}
},
{
"$group": {
"_id": null,
"total": {
"$sum": "$allocation"
}
}
}
])
output:
{
"_id" : null,
"total" : 300
}
Same query running on mongoose in nodejs
let empID = mongoose.Types.ObjectId(work.employee);
await workModel.aggregate([
{
$match: {
$and: [
{ employee: empID },
{
$or: [
{
$and: [
{ start: { $lte: moment(startDayEnd).toISOString() } },
{ end: { $gte: moment(startDayEnd).toISOString() } }
]
},
{
$and: [
{ end: { $gte: moment(endDayEnd).toISOString() } },
{ start: { $lte: moment(endDayEnd).toISOString() } }
]
}
]
}
]
}
},
{
$group: {
_id: null,
total: { $sum: "$allocation" }
}
}
]);
With
mongoose.set('debug', true);
[{
'$match': {
'$and': [{
employee: 5c932e67d7a3cc2530919f75
}, {
'$or': [{
'$and': [{
start: {
'$lte': '2019-01-01T18:29:59.999Z'
}
}, {
end: {
'$gte': '2019-01-01T18:29:59.999Z'
}
}]
}, {
'$and': [{
end: {
'$gte': '2019-01-14T18:30:00.000Z'
}
}, {
start: {
'$lte': '2019-01-14T18:30:00.000Z'
}
}]
}]
}]
}
}, {
'$group': {
_id: null,
total: {
'$sum': '$allocation'
}
}
}], {}
Output
[]
Any help?