0

i am mongodb beginner. Junior JavaScript developer using mongodb/mongoose
i wonder if anyone can help me for this easy challenge :i would like to group results based on dates ( i am able to group and able to filter by date but not both of them )

schema :

const ExpenseSchema = new Schema({
    item: String,
    amount: Number,
    date: Date,
    group: String,
})

enter code here

sample :

{
  "_id": {
    "$oid": "608c4fd352efae8cc8a10100"
  },
  "item": "Amril",
  "amount": 435.42,
  "date": {
    "$date": "2018-05-05T11:06:00.000Z"
  },
  "group": "food",
  "__v": 0
}

My NOT working mongoose query

enter code here

Expense.aggregate([
        {
            $match: 
            {
                date: { $gt: startDate, $lt: endDate }
            }
        },
         {
            $group:
            {
                _id: '$group',
                totalAmount: { $sum: "$amount" }
            }
        }], function (err, total) {
        res.send(total)
    })

thanks in advance

enter image description here

Niro
  • 1
  • see similar question [mongodb group values by multiple fields](https://stackoverflow.com/questions/22932364/mongodb-group-values-by-multiple-fields) you an try something like `$group: { _id; { group: "$group", date: "$date" }, .. }` – turivishal May 09 '21 at 07:29
  • you want to group data based on `date and group fields`?. – Rahul Kumar May 09 '21 at 08:18
  • yes , i want to group the sum of amount for all expenses between given dates. – Niro May 11 '21 at 04:27

1 Answers1

0

solved - changed the query to :

Expense.aggregate([
        {
            $match: {
                date: { $gt: new Date(new Date(startDate).setHours(00, 00, 00)), $lt: new Date(new Date(endDate).setHours(23, 59, 59)) }
            }
        },{$group:{
            _id:'$group',
            amount:{$sum:'$amount'}
        }}
    ], function (err, total) {
        res.send(total)
    })

    
})

added :

 { $gt: new Date(new Date(startDate).setHours(00, 00, 00)), $lt: new Date(new Date(endDate).setHours(23, 59, 59)) }

instead of :

{ $gt: startDate, $lt: endDate }

based on this article : https://dev.to/itz_giddy/how-to-query-documents-in-mongodb-that-fall-within-a-specified-date-range-using-mongoose-and-node-524a

Niro
  • 1