Mongo Db aggregation pipeline with loop

Question

I have a collection which contains half million data, and i want an average for all months between the date i enter. Right now i am getting the data for whole year, but i want it seperated by single month i.e 12 data range for every single month.

Below is the aggregation pipeline i am using.

let filter = 'y';
const date = new Date();
let checkDate = moment().subtract(1.5, 'years')._d;
MeterData.aggregate([
      {
          $group: {
              _id: "$meter_id",
              // total: { $sum: 1 },
              totalEnergy: filter !== 'a' ? {
                $sum: {
                  $toDouble: {
                      $cond: {
                        if: {
                          $gte: [
                            "$date", checkDate
                          ]
                        },
                        then: "$energy.Energy",
                        else: 0
                      }
                }
                                  }
               } : { $sum: { $toDouble: 
                    "$energy.Energy"
                   } }
          },
               }
  ]);

Here i am getting totalEnergy for all year, in totalEnergy field, but now i want totalEnergy plus monthly calculations for the year i enter. Any idea on how to do that. ?

Below is a sample document from the collection.

{"_id":{"$oid":"5e557779ed588826d84cef11"},
"meter_id":"1001",
"date":{"$date":{"$numberLong":"1509474600000"}},
"parameter_name":"hvac","voltage":{"unit":"V"},
"current":{"unit":"AMP"},
"powerFactor":{"unit":"phi"},
"angle":{"unit":"degree"},
"activePower":{"unit":"kwh"},
"reactivePower":{"unit":"kwh"},
"apparentPower":{"unit":"kwh"},
"frequency":{"unit":"hz"},
"thd":{"unit":"percentage"},
"energy":{"Energy":"5.7"},
"power":{"unit":"watt"},

As per suggested by Ryan Gunner, i got my answer which i am pasting below, i just have one more problem.

[
  {
    meter_id: '1001',
    month: '2017-10',
    totalEnergy: 0,
    averageEnergy: 0
  } + 11 more months......
]

Now what i need is the total of the energy for 12 months. For example total of totalEnergy field for all 12 months in a single variable.

Is it feasible for you to add 2 new fields: month, year ? ...calculated off your date field for your existing data - and added to your app's schema so they are entered for new records …. this would set things up to be both easier and faster to run....if you are going to be repeating this query frequently it would a worthwhile effort..... — Cahaba Data
– Cahaba Data, Commented Mar 3, 2020 at 3:53
I think you should better use method toDate() instead of _d property which might be for internal use of moment only. — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 3, 2020 at 13:31

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2020-03-04 03:00:45Z

1

how about something like this?

var startDate = new ISODate('2020-04-01');
var endDate = new ISODate('2019-04-01');

db.collection.aggregate(
    {
        $match: {
            $expr: {
                $and: [
                    { $gt: ['$date', endDate] },
                    { $lt: ['$date', startDate] }]
            }
        }
    },
    {
        $group: {
            _id: {
                meter: '$meter_id',
                month: { $dateToString: { format: '%Y-%m', date: '$date' } }

            },
            totalEnergy: { $sum: { $toDouble: '$energy.Energy' } },
            averageEnergy: { $avg: { $toDouble: '$energy.Energy' } }
        }
    },
    {
        $project: {
            meter_id: '$_id.meter',
            month: '$_id.month',
            totalEnergy: '$totalEnergy',
            averageEnergy: '$averageEnergy',
            _id: 0

        }

    },
    {
        $sort: { meter_id: 1 }
    }
    {
        $group: {
            _id: null,
            grandTotalEnergy: { $sum: '$totalEnergy' },
            monthlyData: { $push: '$$ROOT' }
        }
    },
    { $project: { _id: 0 } }
)

update: added grandTotalEnergy field and pushed monthlyData to an array.

edited Mar 4, 2020 at 3:00

answered Mar 3, 2020 at 4:46

Dĵ ΝιΓΞΗΛψΚ

5,8044 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dharmik soni Over a year ago

Thanks ! It is working perfectly, but theres just one thing left which i am not getting as expected. I have edited the question for that, can you please check and reply back ?

Dharmik soni Over a year ago

Thank You .! Awesome Solution.

Collectives™ on Stack Overflow

Mongo Db aggregation pipeline with loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related