how to implement Mongodb aggregate group by city and sort sources related to city and output sum

Question

i am using aggregate mongodb i have the following order collection:

[

    {
     _id: '62079dfc71e2b7702c246fc5',
     city: 'Uzbakistan',
     source: 'instagram',
     price: 100000
    },
    {
     _id: '62079e3d71e2b7702c246ffa',
     city: 'Pakistan',
     source: 'telegram',
     price: 12000
    },
    {
     _id: '62079e7571e2b7702c24702c',
     city: 'India',
     source: 'instagram',
     price: 21000
    },
    {
     _id: '6201578e9bb8bd6ec10c5b09',
     city: 'Uzbakistan',
     source: 'twitter',
     price: 18000
    },
    {
     _id: '62020316478928faea9914d8',
     city: 'Pakistan',
     source: 'telegram',
     price: 20000
    },
    ...

]

Now I want to aggregate a group by city and get the total price by source, I want this result:

[

    {
     _id: 'Uzbakistan',
     sources: [
       {
        _id: 'instagram',
        totalPrice: 100000
       },
       {
        _id: 'twitter',
        totalPrice: 18000
       }
     ]
    },
    {
     _id: 'Pakistan',
     sources: [
       {
        _id: 'telegram',
        totalPrice: 32000
       }
     ]
    },
    ...
]

How to correctly implement the aggregate pipeline to get such a result, thanks in advance!!!

Answer 1

1. $group - Group by city and source. $sum for price field as totalPrice.
2. $group - Group by city. Push the documents as a sources array.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        city: "$city",
        source: "$source"
      },
      "totalPrice": {
        "$sum": "$price"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.city",
      "sources": {
        "$push": {
          _id: "$_id.source",
          totalPrice: "$totalPrice"
        }
      }
    }
  }
])

Sample Mongo Playground