Doing a sum with mongo db aggregation framework

Question

I have the following kind of docs in a collection in mongo db

{   _id:xx, 
    iddoc:yy,   
    type1:"sometype1", 
    type2:"sometype2",
    date: 
    { 
      year:2015,
      month:4,
      day:29,
      type:"day"
    },
    count:23 
}

I would like to do a sum over the field count grouping by iddoc for all docs where:

type1 in ["type1A","type1B",...]
where type2 in ["type2A","type2B",...]
date.year: 2015,
date.month: 4,
date.type: "day"
date.day between 4 and 7

I would like then to sort these sums.

I think this is probably easy to do within mongo db aggregation framework but I am new to it and would appreciate a tip to get started.

score 2 · Accepted Answer · answered Jan 12 '15 at 15:47

2

This is straightforward to do with an aggregation pipeline:

db.test.aggregate([
  // Filter the docs based on your criteria
  {$match: {
    type1: {$in: ['type1A', 'type1B']},
    type2: {$in: ['type2A', 'type2B']},
    'date.year': 2015,
    'date.month': 4,
    'date.type': 'day',
    'date.day': {$gte: 4, $lte: 7}
  }},

  // Group by iddoc and count them
  {$group: {
    _id: '$iddoc',
    sum: {$sum: 1}
  }},

  // Sort by sum, descending
  {$sort: {sum: -1}}
])

answered Jan 12 '15 at 15:47

JohnnyHK

305,182
66
621
471

Thank you. Is it possible to not only group by iddoc but also by type1? I.e. a multiple group by – user2175783 Jan 12 '15 at 16:19
1

Yes: `_id: {iddoc: '$iddoc', type1: '$type1'}` – JohnnyHK Jan 12 '15 at 16:23
How would I do a multiple group by on iddoc and type1 say, given the fact that docs with a different type1 have count fields named differently? I.e. if type1 is type1A the count field is named counttype1A, if type1 is type1B the count field is named counttype1B. I would want to sum only on counttype1A and only on counttype1B – user2175783 Jan 12 '15 at 16:30
That's probably best asked as a new question. – JohnnyHK Jan 12 '15 at 16:47

score 1 · Answer 2 · answered Jan 12 '15 at 15:47

If I understood you correctly:

db.col.aggregate
(
  [{
    $match:
    {
      type1: {$in: ["type1A", type1B",...]},
      type2: {$in: ["type2A", type2B",...]},
      "date.year": 2015,
      "date.month": 4,,
      "date.day": {$gte: 4, $lte: 7},
      "date.type": "day" 
    }
  },
  {
    $group:
    {
       _id: "$iddoc",
       total_count: {$sum: "$count"}
    }  
  },
  { $sort: {total_count: 1}}]
)

This is filtering the field date.day between 4 and 7 inclusive (if not, use $gt and $lt to exclude them). And it sorts results from lower to higher (ascending), if you want to do a descending sort, then:

{ $sort: {total_count: -1}}

Doing a sum with mongo db aggregation framework

2 Answers2

Linked