CODEWITHSUNDEEP
mongodbaggregation-framework
Pierre-Louis Gottfrois
Pierre-Louis Gottfrois

Reputation: 17631

Mongo aggregate group by one array element

I have the following documents:

{
  "_id" : ObjectId("540dadfcf3116b60d401c314"),
  "value" : 2,
  "d_c_at" : [
    "2013",
    "201311",
    "2013w46",
    "20131116"
  ]
}

and I want to group them by the last element in the d_c_at array (20131116) which represent the year, month and day store as destructured date.

Here is what I have so far:

db.points.aggregate(
  { $match: { "d_c_at.0": '2014' } },
  { $group: { _id: "$d_c_at.0", value: { $sum: "$value" } } }
)

which return:

{ "_id" : [ ], "value" : 1207 }

I have tried using $unwind without success:

db.points.aggregate(
  { $match: { "d_c_at.0": '2014' } },
  { $unwind: "$d_c_at" },
  { $group: { _id: "$d_c_at", value: { $sum: "$value" } } }
)

Seems almost good but it also groups on other array elements:

{ ... }
{ "_id" : "20140519", "value" : 33 }
{ "_id" : "20140707", "value" : 36 }
{ "_id" : "20140330", "value" : 37 }
{ "_id" : "20140709", "value" : -28 }
{ "_id" : "20140620", "value" : 14 }
{ "_id" : "2014w9", "value" : -250 }
{ ... }

Expected output:

{ ... }
{ "_id" : "20140519", "value" : 33 }
{ "_id" : "20140707", "value" : 36 }
{ "_id" : "20140330", "value" : 37 }
{ "_id" : "20140709", "value" : -28 }
{ "_id" : "20140620", "value" : 14 }
{ ... }

Upvotes: 1

Views: 822

Answers (1)

BatScream
BatScream

Reputation: 19700

Though this might be achievable using aggregation, Your expected output is easily achievable using Map-reduce: Assuming your d_c_at, always has 4 elements, or the 4th element being your group id criteria, as your example structure depicts.

emit key as the 4th element, so that the documents are grouped by the 4th element of "d_c_at".

var map = function(){emit(this.d_c_at[3],{"sum":this.value});}

Once this is done, calculate the sum:

var reduce = function(id,Arr){
var sum = 0;
for(var i=0;i<Arr.length;i++)
    {
        var obj = Arr[i];
        var value = obj.sum;
        sum = sum+value;
    }
    return {"sum":sum};
}

Dump the result onto "output".

db.test.mapReduce(
                     map,
                     reduce,
                     { out: "output" }
                   )

o/p:

> db.output.find()
{ "_id" : "20131116", "value" : { "sum" : 6 } }
{ "_id" : "20131117", "value" : { "sum" : 6 } }

Sample i/p used:

{
  "_id" : 1,
  "value" : 2,
  "d_c_at" : [
    "2013",
    "201311",
    "2013w46",
    "20131116"
  ]
}

{
  "_id" : 2,
  "value" : 4,
  "d_c_at" : [
    "2013",
    "201311",
    "2013w46",
    "20131116"
  ]
}

{
  "_id" : 3,
  "value" : 6,
  "d_c_at" : [
    "2013",
    "201311",
    "2013w46",
    "20131116"
  ]
}

Upvotes: 3

Related Questions