MongoDB aggregate from array field [duplicate]

Question

I have the following collection:

{
  "_id" : ObjectId("52e7aa3ed3d55b9b01e23f34"),
  "time" : mytime,
  "type_instance" : "",
  "values" : [0.23, 0.08, 0.06],
  "types" : ["type0", "type1", "type2"]
}

I want to group by time to get the average of the values per index. The desired result would be something like:

{
  "time" : mytime,
  "values" : [avg 0, avg 1, avg 2],
  "types" : ["type0", "type1", "type2"]
}

I tried to aggregate

collection.aggregate([
                   {   "$match": {'time':  {"$gte": start}

                                 } 
                   }
                   ,{    "$project": {
                           "time":"$time",
                           "values":  "$values"                   
                       }
                   }

                   ,{   
                       "$group": {"_id": "$time", "avg": {avg:"$values[0]"}}
                   }

                   ,{
                       "$sort": {"time": 1}
                   }
                      ], function(err, data) {});

Off course this doesn't work, I can't use "$values[0]". Is there a way to do this?

I tink the model is quite different: {{value, type},{value, type}} Vs {{value1, value2},{type1, type2}} — user3265121
– user3265121, Commented Feb 4, 2014 at 14:04
I guess what you want isn't possible at the moment jira.mongodb.org/browse/SERVER-4589 , accessing an array element like $values.0 only works in the $match step (aggregation) or in normal queries — joao
– joao, Commented Feb 4, 2014 at 14:20

jonathanwiesel · Accepted Answer · 2014-02-07 21:05:42Z

I think the problem could be with your document structure because your want to link indirectly the values from the values field to the ones in types field, maybe something like this would be more convenient:

{
   "_id": ObjectId("52e7aa3ed3d55b9b01e23f34"),
   "time" : mytime,
   "type_instance" : "",
   "whatever":[{
        "type": 0,
        "value": 0.23
   },{
        "type": 1,
        "value": 0.08
   },{
        "type": 2,
        "value": 0.06
   }]
}

This way you could group by time and type (or index as I think you referred to it) after unwinding the whatever field:

collection.aggregate([
    {$unwind: "$whatever"},
    {$match: {"time": ...},
    {$group:{
        _id: {"$time", "$whatever.type"},
        avg: {$avg: "$whatever.value"}
    }}
])

This way you will get N documents per time group, being N = number of types or subdocuments in the whatever field.

Collectives™ on Stack Overflow

MongoDB aggregate from array field [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related