How to show columns which are not part of $group clause in aggregate query

Question

I would like to output result of my $unwind operation into another collection and getting duplicate _id error. In order to overcome it I am using $group to produce a new _id field. How can I project extra fields from collection? When I am using $project clause it doesn't output anything.

This one works:

db.audit.aggregate([
{$match: { "date": { $gte : ISODate("2015-01-30T00:00:00.000Z"),
                     $lt : ISODate("2015-01-31T00:00:00.000Z")
                    }
                }
            },
{ $unwind : "$data.items" } ,

{ $group : {
    "_id" : {
         "_id":"$_id",
         "sku":"$data.items.sku"
            }
    } 
},
{ 
    $out : "randomAggregates"
}
]
)

This one doesn't:

db.audit.aggregate([
{$match: { "date": { $gte : ISODate("2015-01-30T00:00:00.000Z"),
                     $lt : ISODate("2015-01-31T00:00:00.000Z")
                    }
                }
            },
{ $unwind : "$data.items" } ,
{ $project: { "ccy" : "$data.crncy",  
              "coo" : "$data.items.coo",
              "order_id" : "$meta.refid"
            }
},
{ $group : {
    "_id" : {
         "_id":"$_id",
         "sku":"$data.items.sku"
            }
    } 
},
{ 
    $out : "randomAggregates"
}
]
)

Thank you

"works / doesn't work" are you getting an exception? error? some more info might help. — djv

BatScream BatScream · Accepted Answer · 2015-02-03T17:54:19

Use the $first operator in the $group stage and access the whole document using the $$ROOT variable.

{$group:{"_id":{"_id":"$_id","sku":"$data.items.sku"},
         "value":{$first:"$$ROOT"}}}

or,

   {$group:{"_id":{"_id":"$_id","sku":"$data.items.sku"},
             "field":{$first:"$field"}}}  // do this for all the fields.

Your modified code would look like:

db.audit.aggregate([
{$match: { "date": { $gte : ISODate("2015-01-30T00:00:00.000Z"),
                     $lt : ISODate("2015-01-31T00:00:00.000Z")
                    }
                }
            },
{ $unwind : "$data.items" } ,
{$group:{"_id":{"_id":"$_id","sku":"$data.items.sku"},
         "value":{$first:"$$ROOT"}}}   // you could do it for individual 
                                       // fields as well here
{ 
    $out : "randomAggregates"
}
]
)

How to show columns which are not part of $group clause in aggregate query

1 Answers