MongoDb Aggregate per subdocument

Question

I have these collections, stats and items. Stats has one item as subdocument:

var ItemSchema = new Schema({ 
    type: String,
    created_at: Date,
    updated_at: {
        type: Date,
        default: Date.now()
    }
});

var StatsSchema = new Schema({ 
    item: {
        type: Schema.ObjectId,
        ref: 'Item'
    },
    url: String,
    date: Date,
    action: String,
    hourly: Number
});

I'd like to aggregate Stats grouping by item.type. Is it possible?

I tried something like this but without luck:

db.stats.aggregate(
    { $project: { _id: 1, hourly: 1, action: 1, item: 1, type: '$item.type' } }, 
    { $group: { _id: { action: '$action', type: '$item.type' }, total: { $sum: '$hourly' } } }
)

You have a referenced schema with information stored in separate collections. MongoDB does not "join" data across collections with operations like this. If you need this type of action you would be better of "embedding" the data instead. — Neil Lunn, Mar 03 '15 at 04:33

Kevin Brady · Answer 1 · 2015-02-03T11:08:07.487

0

You should not need the $project part of the pipeline. You should be get what you need from the $group stage

db.stats.aggregate({ $group: { _id: "$item.type" , total: { $sum: '$hourly' } } });

edited Feb 03 '15 at 11:08

answered Feb 02 '15 at 22:11

Kevin Brady

1,684
17
30

Tried but no way to get it. – michelem Feb 03 '15 at 10:04
can you add more detail as to what error you get or what steps you are taking to help troubleshoot? what happens when you try and execute the aggregation query above? Can you add some sample data to your initial query? – Kevin Brady Feb 03 '15 at 11:09
It works, no errors, but _id is null with "_id: "$item.type"" – michelem Feb 03 '15 at 11:36
1

can you provide some sample data in the stats collection? (add it to the initial question) – Kevin Brady Feb 03 '15 at 11:44

MongoDb Aggregate per subdocument

1 Answers1