0

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 know now how to do this (see this question) 

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

but would like some of the fields in the match operation to appear in the final document. Is this possible? How?

Community
  • 1
  • 1
user2175783
  • 1,291
  • 1
  • 12
  • 28
  • It would probably be a good idea to include the query as you currently have it. I know it's essentially shown in the answer to your previous question, but it would be nice to have for reference in answers to this question. – NoOutlet Jan 12 '15 at 18:19
  • Good point I added it above. – user2175783 Jan 12 '15 at 18:54

1 Answers1

6

I believe that this query is a solution for what you are asking:

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 type1 and count them
  {$group: {
    _id: { iddoc: '$iddoc', type1: '$type1' },
    sum: {$sum: 1},
    type2: { $push: '$type2' },
    year: { $first: '$date.year' },
    month: { $first: '$date.month' },
    day: { $addToSet: '$date.day' }
  }},

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

There are some options with how you want to see the rest of the fields. I chose to push the type2 to an array (allowing for duplicates), take the first value for year and month since those will always be 2015 and 4 per your match operation, and addToSet the day to an array (not allowing for duplicates). Another option would be to push the entire document into an array of matches, but one should be careful with that on large collections.

{$group: {
    _id: { iddoc: '$iddoc', type1: '$type1' },
    sum: {$sum: 1},
    matches: { $push: '$$ROOT' }
  }},
NoOutlet
  • 1,949
  • 1
  • 14
  • 22
  • Thank you. When I run this query it tells me that I cant use $date.year or $name.subname in the $group stage because group field names can not contain . in them. I imagine this is a mongodb limitation? – user2175783 Jan 13 '15 at 15:40
  • Well, "$name.subname" does not appear in the query I wrote in my answer, so you are certainly not running the query exactly as written. Also, in my query, '$date.year' is not being used as a field name - it is referencing the value of 'year' within the 'date' object. I can't figure out exactly how to help you resolve it without seeing the exact query you are actually inputting.... However I can give a clue: the field names in the first query I have in my answer are "_id", "sum", "type2", "year", "month", and "day". Those are the parts of the `$group` that can't look like `'$date.year'`. – NoOutlet Jan 13 '15 at 16:13
  • Were you able to resolve that issue? Did you want to get more help with it? – NoOutlet Jan 13 '15 at 21:29
  • I was thank you. One other question (which might be impossible) is this: When I group by iddoc and type1 I actually want to sum over a field in the doc called count. Unfortunately that field's name is different for different type1 types. Is there a way of summing over each of these separately or the only option is not to do the group by type1 and loop through the types doing a group by iddoc for each? – user2175783 Jan 14 '15 at 19:28
  • I don't understand precisely the two potential solutions you're considering there but it sounds like a complicated enough question (and I know you've heard this before) that it should be a separate question. If I've solved the problem you were asking about in this question, please be sure to tick my solution. Thanks and I look forward to your next question! – NoOutlet Jan 14 '15 at 20:00