How to calculate average on multiple field using MongoDB?

Question

I have collection in that collection 3 field. These field is in arrayIndex format. Collection sample as follows

    { 
        "_id" : ObjectId("576165f58d8b8f39458b456c"), 
        "EventTS" : ISODate("2016-06-07T03:30:00.000+0000"), 
        "PowerStatus" : [
           NumberInt(1), 
           NumberInt(1), 
           null, 
           NumberInt(1), 
           null
         ], 
        "TempStatus" : [
           NumberInt(1), 
           null, 
           NumberInt(1), 
           null, 
           null
        ], 
        "UPSStatus" : [
           NumberInt(1), 
           null, 
           NumberInt(1)
        ]
   }

This is my sample of collection one record. Basically PowerStatus, TempStatus, and UPSStatus is arrayindex format. In this field data stored 1 or null value only. Firstly i have to count 1 in individual filed after that I have to calculate the average of PowerStatus, TempStatus, and UPSStatus. And I try to get result in this format as shown below

  {
  "result": [
    {
      "PowerStatus": 77,
      "TempStatus": 12,
      "UPSStatus": 11
    }
  ]
}

When query excute after that i want output Like in collection average of PowerStatus is 77, TempStatus is 12 and UPSStatus is 11. Please suggest me how to calculate average of 3 filed?

Possible duplicate of [Mongo average aggregation query with no group](http://stackoverflow.com/questions/23323515/mongo-average-aggregation-query-with-no-group) — Ali Dehghani, Jun 24 '16 at 13:31
I think it's different first i have to count of 1 in PowerStatus, TempStatus, UPSStatus field after that i have calculate average. @AliDehghani — sandip kakade, Jun 24 '16 at 13:36
Your picture is kinda misleading (at least for me). Please replace it with a corresponding schema text or sample documents — Ali Dehghani, Jun 24 '16 at 13:38
Please see [this](http://stackoverflow.com/questions/37932924/how-to-solve-mongodb-related-issue-efficiently) using robomongo press F4 to get text result — profesor79, Jun 24 '16 at 13:49
@profesor79 now i just implement your response but not recevied any response — sandip kakade, Jun 25 '16 at 09:43

score 0 · Answer 1 · answered Jun 24 '16 at 14:12

You can use aggregation framework for that.

As we don't have your data set we cannot chek results - but see below proposed solution for ypur problem;

db.dhsari.aggregate([{
            $unwind : "$PowerStatus"
        }, {
            $unwind : "$TempStatus"
        }, {
            $unwind : "$UPSStatus"
        }, {
            $group : {
                _id : null,
                PowerStatus : {
                    $avg : "$PowerStatus"
                },
                TempStatus : {
                    $avg : "$TempStatus"
                },
                UPSStatus : {
                    $avg : "$UPSStatus"
                },
            }
        }, {
            $project : {
                _id : 0,
                result : [{
                        "PowerStatus" : "$PowerStatus",
                        "TempStatus" : "$TempStatus",
                        "UPSStatus" : "$UPSStatus"
                    }
                ]

            }
        }

    ])

How to calculate average on multiple field using MongoDB?

1 Answers1