CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
G.Mounika
G.Mounika

Reputation: 425

How to get day wise records of particular month in mongodb?

This is my schema

empname: {type: String},
soldby: {type: String},
expenseprice:{type:Number},
expensedesc:{type:String},
expensetype:{type:String},
createdat: {type: Date, default: Date.now}

I tried this query

db.expenses.aggregate([
    {
        $project: {
            _id: 1,
            year: { $year: "$createdat" },
            month: { $month: "$createdat" },
            day: { $dayOfMonth: "$createdat" },
            expenseprice: 1
        }
    },
    {
        $group: {
           _id: {
              year: "$year",
              month: "$month",
              day: "$day"
           },
           sum: { $sum: "$expenseprice" }
        }
    }
])

I'm getting output as

{ "_id" : { "year" : 2015, "month" : 8, "day" : 15 }, "sum" : 200 }
{ "_id" : { "year" : 2016, "month" : 5, "day" : 20 }, "sum" : 150 }
{ "_id" : { "year" : 2016, "month" : 6, "day" : 29 }, "sum" : 250 }

I want only the records of particular year and particular month and in that month, day wise like this

{ "_id" : { "year" : 2016, "month" : 6, "day" : 28 }, "sum" : 150 }
{ "_id" : { "year" : 2016, "month" : 6, "day" : 29 }, "sum" : 200 }

I tried $match too

db.expenses.aggregate([
    {
        $project: {
            _id: 1,
            year: { $year: "$createdat" },
            month: { $month: "$createdat" },
            day: { $dayOfMonth: "$createdat" },
            expenseprice: 1
        }
    },
    {
        $match: {
            $eq: ["$month", 06]
        }
    },
    {
        $group: {
            _id: {
                year: "$year",
                month: "$month",
                day: "$day"
            },
            sum: { $sum: "$expenseprice" }
        }
    }
])

But I'm getting error like this

assert: command failed: {
    "ok" : 0,
    "errmsg" : "bad query: BadValue unknown top level operator: $eq",
    "code" : 16810
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:23:13
doassert@src/mongo/shell/assert.js:13:14
assert.commandWorked@src/mongo/shell/assert.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1215:5
@(shell):1:1

2016-06-29T16:20:47.754+0530 E QUERY    [thread1] Error: command failed: {
    "ok" : 0,
    "errmsg" : "bad query: BadValue unknown top level operator: $eq",
    "code" : 16810
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:23:13
doassert@src/mongo/shell/assert.js:13:14
assert.commandWorked@src/mongo/shell/assert.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1215:5
@(shell):1:1

Upvotes: 1

Views: 1565

Answers (1)

notionquest
notionquest

Reputation: 39196

Please refer the below sample for how the year and month in $group can be referred. As per my understanding, that is the last bit you need for your requirement.

db.expenses.aggregate([
   {$project: {_id:1,year:{$year:"$createdat"},month:{$month:"$createdat"}, day:{$dayOfMonth:"$createdat"},expenseprice:1}},
   {$group: {_id:{year:"$year",month:"$month",day:"$day"}, sum:{$sum:"$expenseprice"}}},
   {$match : {"_id.year" : 2016, "_id.month" : 6}}])

I am not sure why you have added another group after the match. From my initial understanding, it may not be required. If the above solution is not as expected, please update the comment or requirement. I will adjust the answer accordingly.

I have tested my query with some sample data. The data is filtered for a month and I get the day wise breakdown.

Upvotes: 2

Related Questions