Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get original document field as part of aggregate result

I am wanting to get all of the document fields in my aggregate results but as soon as I use $group they are gone. Using $project allows me to readd whatever fields I have defined in $group but no luck on getting the other fields:

var doc = {
  _id: '123',
  name: 'Bob',
  comments: [],
  attendances: [{
    answer: 'yes'
  }, { 
   answer: 'no'
  }]       
}

aggregate({
    $unwind: '$attendances'
  }, {
    $match: {
      "attendances.answer": { $ne:"no" }
    }
  }, {
    $group: {
    _id: '$_id',
    attendances: { $sum: 1 },
    comments: {  $sum: { $size: { $ifNull: [ "$comments", [] ] }}}
  } 
}, {
  $project: {
    comments: 1,
  }
}

This results in:

[{
  _id: 5317b771b6504bd4a32395be,
  comments: 12 
},{
  _id: 53349213cb41af00009a94d0,
  comments: 0 
}]

How do I get 'name' in there? I have tried adding to $group as:

name: '$name'

as well as in $project:

name: 1

But neither will work

like image 334
cyberwombat Avatar asked Aug 03 '14 20:08

cyberwombat


1 Answers

You can't project fields that are removed during the $group operation.

Since you are grouping by the original document _id and there will only be one name value, you can preserve the name field using $first:

db.sample.aggregate(
    { $group: {
        _id: '$_id',
        comments: {  $sum: { $size: { $ifNull: [ "$comments", [] ] }}},
        name: { $first: "$name" }
    }}
)

Example output would be: { "_id" : "123", "comments" : 0, "name" : "Bob" }

If you are grouping by criteria where there could be multiple values to preserve, you should either $push to an array in the $group or use $addToSet if you only want unique names.

Projecting all the fields

If you are using MongoDB 2.6 and want to get all of the original document fields (not just name) without listing them individually you can use the aggregation variable $$ROOT in place of a specific field name.

like image 118
Stennie Avatar answered Oct 31 '22 11:10

Stennie