CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Ferguzz
Ferguzz

Reputation: 6077

mongodb aggregating nested values across whole collection with counts

I have documents with the following structure:

{
    _id: 123,
    machine_id: 456,
    data: {
        some_data: 100,
        exceptions: [{
            hash: 789,
            value: 'something',
            stack_trace: 'line 123: oops',
            count: 5,
        }]
    }
}

{
    _id: 234,
    machine_id: 567,
    data: {
        some_other_data: 200,
        exceptions: [{
            hash: 789,
            value: 'something',
            stack_trace: 'line 123: oops',
            count: 1,
        }, {
            hash: 890,
            value: 'something_else',
            stack_trace: 'line 678: ouch',
            count: 3,
        }]
    }
}

The hash is a combination of the value and stack_trace (I added this specifically to try to aggregate exceptions across the whole collection). I want to run a query which returns each distinct exception, along with the total count, and the value and stack trace. In this case the result would look something like:

[{
    hash: 789,
    value: 'something',
    stack_trace: 'line123: oops',
    count: 6,
}, {
    hash: 890,
    value: 'something_else',
    stack_trace: 'line 678: ouch',
    count: 3,
}]

I'm quite new to mongoDB, and sturggling to get the aggregation pipeline stages to give me any meaningful output.

Would also welcome comments on structuring this data, if you think there is a better way.

Upvotes: 0

Views: 325

Answers (1)

s7vr
s7vr

Reputation: 75914

Your structure looks fine. You can drop the hash if you want and use value and stack_trace as a grouping key.

You can use below aggregation.

For $grouping on hash, you will need to $unwind exceptions embedded array followed by $first to keep the value and stack_trace and $sum to count no of distinct exceptions. 

db.collection.aggregate(
{$unwind:"$data.exceptions"}, 
{$group:{_id:"$data.exceptions.hash", value:{$first:"$data.exceptions.value"}, stack_trace:{$first:"$data.exceptions.stack_trace"}, count:{$sum:"$data.exceptions.count"}}})

Upvotes: 2

Related Questions