Group query in mongoDB using array items to calculate sum

Question

Each document in my collection has a property that is an array and that array has objects with properties such as total, index, etc. I want to group the documents by the sum of the total property of the second array element, like the query below. How can I make it happen?

I want to make this query work :

db.nwt_envio_contacto.aggregate({  
   $project:{  
      "visualizacoes":true,
      "envio_id":true
   }   ‌
},
{  
   $match:{  
      "envio_id":ObjectId("54c5ed8101ec09c26c7b23c6"),
      "visualizacoes":{  
         $elem‌Match:{  
            "index":1
         }
      }
   }
},
{  
   $group:{  
      _id:null,
      total:{  
         $sum:"visualizacoes.$.total"
      }
   }
})

Assume that I have a collection with this document :

{
   "_id":ObjectId("54c5ed8101ec09c26c7b23c8"),
   "envio_id":ObjectId("54c5ed8101ec09c26c7b23c6"),
   "visualizacoes":[
      {
         "primeira":"2015-01-26 09:34:58",
         "ultima":"2015-01-26 14:17:18",
         "index":2,
         "total":4
      },
      {
         "primeira":"2015-01-26 09:35:13",
         "ultima":"2015-01-26 09:35:43",
         "index":2,
         "total":3
      }
   ]
}

Basically I want to sum the total attribute of all visualizacoes objects that have the index equal to 1.

Can you clarify that a little more? Post a sample document and clearly explain the information you need to retrieve. — joao
– joao, Commented Jan 26, 2015 at 13:45
Assume that I have a collection with this document : { "_id" : ObjectId("54c5ed8101ec09c26c7b23c8"), "envio_id" : ObjectId("54c5ed8101ec09c26c7b23c6"), "visualizacoes" : [ { "primeira" : "2015-01-26 09:34:58", "ultima" : "2015-01-26 14:17:18","index" : 2, "total" : 4 }, { "primeira" : "2015-01-26 09:35:13", "ultima" : "2015-01-26 09:35:43", "index" : 2, "total" : 3 } ] } — m_junior
– m_junior, Commented Jan 26, 2015 at 16:32
I want to make this query work : db.nwt_envio_contacto.aggregate({$project:{"visualizacoes":true,"envio_id":true}},{$match:{"envio_id":ObjectId("54c5ed8101ec09c26c7b23c6"),"visualizacoes":{$elemMatch:{"index":1}}}}, {$group:{_id:null,total:{$sum:"visualizacoes.$.total"}}}) — m_junior
– m_junior, Commented Jan 26, 2015 at 16:33
Basically i want to sum the total atribute of all visualizacoes objects that have the index equal to 1. Don't say that I have no visualizacoes with index equal to 1, because I only showed one document of my collection. I have many. — m_junior
– m_junior, Commented Jan 26, 2015 at 16:35

joao · Accepted Answer · 2015-01-26 18:02:16Z

1

This aggregation will sum all visualizacoes objects that have the index equal to 1:

db.nwt_envio_contacto.aggregate([
    {$match:{
        "envio_id":ObjectId("54c5ed8101ec09c26c7b23c6")
    }},
    {$unwind: "$visualizacoes"},
    {$match:{
        "visualizacoes.index":1
    }},
    {$group:{
        _id:null,
        total:{  
            $sum:"$visualizacoes.total"
        }
    }}
])

You can use the $unwind operator to flatten the array and after filtering the entries where the index is 1, you are ready to sum.

answered Jan 26, 2015 at 18:02

joao

4,1273 gold badges35 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Group query in mongoDB using array items to calculate sum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related