CODEWITHSUNDEEP
mongodbmongoosemongodb-query
Naresh Varun Guttula
Naresh Varun Guttula

Reputation: 123

Getting Mongo results from only previous month avg by default

I have this data in mongodb:

 [
{

    "CreateDate" : ISODate("2018-08-08T04:53:51.840Z"),
    "cummulativeKWH" : 368.5,
     "frequency" : "50.12"
},
{

    "CreateDate" : ISODate("2018-08-08T04:53:51.840Z"),
    "cummulativeKWH" : 369.5,
     "frequency" : "50.12"
},
{

    "CreateDate" : ISODate("2018-09-03T04:53:51.840Z"),
    "cummulativeKWH" : 369.5,
     "frequency" : "50.12"
},
]

I am new to MongoDb and would appreciate some help with this query. I wrote the following aggregation pipeline. I need to take the average of cummulativeKWH for that previous month only I am using filter methods and selected dates. How to get last previous month avg of cummulativeKWH (ex:aug 1-to-aug 31) only by default without selection and without filters.
Anyone please suggest me.

 db.collection.aggregate([
  { "$match": {
    'createDate': {'$gte':ISODate("2018-08-01"), '$lte':ISODate("2018-08-31") } 
  }},
  {$group: {_id:null,
   cummulative: {$avg:"$cummulativeKWH"}
}}
])

I am trying this aggregation I got the output but how to get without date selection and without filters how to get default previous month avg of data.

Upvotes: 0

Views: 1895

Answers (1)

matthPen
matthPen

Reputation: 4353

Since 3.6 version

You have to extract year and month from your date. try below aggregation

db['02'].aggregate(
    [
        // Stage 1
        {
            $match: {
                $expr: {$and:[
                  {$eq:[{$year:"$CreateDate"},{$year:new Date()}]},
                  {$eq:[1,{$subtract:[{$month:new Date()},{$month:"$CreateDate"}]}]},  
                ]}
            }
        },

        // Stage 2
        {
            $group: {
            _id:{year:{$year:"$CreateDate"},month:{$month:"$CreateDate"}},
                    avg : {$avg:"$cummulativeKWH"}

            }
        },
    ],
);

Before version 3.6 you can use $redact stage :

db['02'].aggregate(

// Pipeline
[
    // Stage 1
    {
        $group: {
        _id:{year:{$year:"$CreateDate"},month:{$month:"$CreateDate"}},
                avg : {$avg:"$cummulativeKWH"},       
        }
    },

    // Stage 2
    {
        $redact: {
          $cond: {  if: { $and:[
            {$eq: [ "$_id.year", {$year:new Date()} ]}, 
            {$eq: [-1, {$subtract:[ "$_id.month", {$month:new Date()} ]}]}
            ]},
                  then: "$$KEEP",
                  else: "$$PRUNE"
          }
        }
    },
],

);

Result : 

{ 
    "_id" : {
        "year" : NumberInt(2018), 
        "month" : NumberInt(8)
    }, 
    "avg" : 369.0
}

Upvotes: 3

Related Questions