Aggregation on aggregate

Question

We have a collection as follows - Batches(0,1,2 etc) running jobs in chunks.
Each batch would have multiple chunks/documents having jobs.

We need to aggregate jobs for each batch.
Thereafter,job count of each batch needs to be subtracted from that of batch '0'.

The result would be an array/collection of the difference of job counts of each batch with batch 0 respectively.
If there are 'x' batches,the size of the array would be x-1 obviously.

Can this be done in a single aggregation operation?

{
    "_id": ObjectId("xxxxxx"),
    "batch": ["0"],
    "jobs":2500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["1"],
    "jobs":1500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["0"],
    "jobs":1500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["2"],
    "jobs":3500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["1"],
    "jobs":500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["0"],
    "jobs":1500
},
{
    "_id": ObjectId("xxxxxx"),
    "batch": ["3"],
    "jobs":2500
},

Expected output:

[ 3500,2000,3000]

score 0 · Answer 1 · answered Feb 19 '20 at 19:07

You can try this :

db.collection.aggregate([
    { $unwind: '$batch' },
    { $group: { _id: '$batch', count: { $sum: '$jobs' } } },
    { $group: { _id: '', data: { $push: '$$ROOT' } } },
    {
        $addFields: {
            value: { $filter: { input: '$data', as: 'e', cond: { $eq: ['$$e._id', '0'] } } }
        }
    },
    {
        $project: {
            _id: 0,
            data: { $filter: { input: { $map: { input: '$data', as: 'e', in: { $subtract: [{ $arrayElemAt: ['$value.count', 0] }, '$$e.count'] } } }, as: 'e', cond: { $ne: ['$$e', 0] } } }
        }
    }])

Test : MongoDB-Playground

Aggregation on aggregate

1 Answers1