Mongo aggregate group by one array element

Question

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 }
{ ... }

Why are you storing the date this way? You can use date expression operators to group on parts of a date. — wdberkeley
– wdberkeley, Commented Sep 8, 2014 at 19:19
Why not copy the match in the aggregate after the unwind? I'm not sure what the expected output should be. — Larry Battle
– Larry Battle, Commented Sep 8, 2014 at 20:17
@wdberkeley better performance when querying documents created on a particular year, week, day — Pierre-Louis Gottfrois
– Pierre-Louis Gottfrois, Commented Sep 9, 2014 at 7:10
@LarryBattle edited by question with expected output. Basically I want the sum of the value group on days. — Pierre-Louis Gottfrois
– Pierre-Louis Gottfrois, Commented Sep 9, 2014 at 7:11
@Pierre-LouisGottfrois: in order to achieve better performance, you have a) a multikey index, b) an additional unwind unwind stage in your aggregation (delaying an early match), c) massively increased data size, d) string matching. Sorry, but think again. Using data expression operators is by far the better solution, on all levels. — Markus W Mahlberg
– Markus W Mahlberg, Commented Sep 9, 2014 at 7:53

BatScream · Accepted Answer · 2014-09-09 08:22:23Z

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"
  ]
}

Collectives™ on Stack Overflow

Mongo aggregate group by one array element

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