0

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?

Thamaraiselvam
  • 6,961
  • 8
  • 45
  • 71
  • 2
    And with your schema definition, does it say something like `mongoose.model('WorkModel', workModelSchema)` ? Point being that first bit is it more like `"WorkModel'` or did you actually call it `'Work'`. – Neil Lunn Mar 28 '19 at 07:30
  • @NeilLunn Yes, it is – Thamaraiselvam Mar 28 '19 at 07:31
  • No problem with `workModel` it works fine with other queries. `export const workModel = mongoose.model('Work', workSchema);` – Thamaraiselvam Mar 28 '19 at 07:33
  • 1
    So you should call it `"Work"` so it actually translates to the collection `"works"`, and the other problem I just spotted is you are using a "string" for ObjectId. Mongoose cannot cast a "sting" to `ObjectId` in an aggregation pipeline. – Neil Lunn Mar 28 '19 at 07:33
  • I tried to caste it before pipe line even that does not help. – Thamaraiselvam Mar 28 '19 at 07:35
  • Sorry I meant "dates". They need to be `Date` types in JavaScript and not strings. Wrong dupe, I'll switch it. `.toDate()` from momentjs instead of `.toString()`. – Neil Lunn Mar 28 '19 at 07:36
  • Awesome, @NeilLunn it worked `new Date(startDayEnd)` – Thamaraiselvam Mar 28 '19 at 07:38

0 Answers0