Grouping objects in array fields and saving the results to a document

Say I have two documents that contain sales information.

Document 1

{
    _id: ISODate("2022-07-24"),
    week: 30,
    productCounts: [
        {
            id: 34528,
            count: 8
        },
        {
            id: 34556,
            count: 6
        }
    ]
}

Document 2

{
    _id: ISODate("2022-07-25"),
    week: 30,
    productCounts: [
        {
            id: 34528,
            count: 4
        },
        {
            id: 34556,
            count: 6
        }
    ]
}

My goal is to “merge” these two documents to produce another document like this:

{
    _id: { week: 30 },
    productCounts: [
        {
            id: 34528,
            count: 12
        },
        {
            id: 34556,
            count: 12
        }
    ]
}

I am trying to apply the concept from Example 1, Alternative 1 on this page: https://www.mongodb.com/docs/manual/reference/map-reduce-to-aggregation-pipeline/

At the moment, my aggregation pipeline looks like this:

[
    {
        $unwind: {
            path: '$productCounts'
        }
    }, 
    {
        $group: {
            _id: 'id',
            totalCount: {
                $sum: '$count'
            }
        }
    }
]

How can I use the aggregation pipeline to create a single document containing the results from the group stage?

I think the only issues with your pipeline is that you are using

and

rather than

_id : "$productCounts.id"

and

$sum: '$productCounts.count'

To understand, remove the $group stage entirely and analyze the result of $unwind.

I just notice you wanted _id : week, which won’t be fixed with the changes.

I bookmarked back your post to work on it later.

I think I nailed it.

unwind = { "$unwind" : "$productCounts" }
group_week_id = {
    '$group': {
      _id: { week: '$week', id: '$productCounts.id' },
      count: { '$sum': '$productCounts.count' }
    }
  }
group_week = {
    '$group': {
      _id: { week: '$_id.week' },
      productCounts: { '$push': { id: '$_id.id', count: '$count' } }
    }
  }
pipeline = [ unwind , group_week_id , group_week ]
db.sales.aggregate( pipeline )
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.