I have an array field (contains objects) in multiple documents, I want to merge the arrays into one array and group the array by object key. I have manage to group the array but I dont know how to group the data. See the code I tried below

const test = await salesModel.aggregate([
            { $unwind: "$items" },
            {
                $group: {
                    _id: 0,
                    data: { $addToSet: '$items' }
                },
            }
        ])

Result of the query:

{
  _id: 0,
  data: [
    {
      _id: 61435b3c0f773abaf77a367e,
      price: 3000,
      type: 'service',
      sellerId: 61307abca667678553be81cb,
    },
    {
      _id: 613115808330be818abaa613,
      price: 788,
      type: 'product',
      sellerId: 61307abca667678553be81cb,
    },
    {
      _id: 61307c1ea667676078be81cc,
      price: 1200,
      type: 'product',
      sellerId: 61307abca667678553be81cb,
    }
  ]
}

Now I want to group the data array by object key data.sellerId and sum price

Desired Output:

{
  data: [
    {
      sumPrice: 788,
      sellerId: 613115808330be818abaa613,
    },
    {
      sumPrice: 1200,
      sellerId: 61307abca667678553be81cb,
    }
  ]
}

CodePudding user response：

Extend with the current query and result with:

1. $unwind: Deconstruct the array field to multiple documents.
2. $group: Group by data.sellerId to sum ($sum) for data.price.
3. $group: Group by 0 with $addToSet to combine multiple documents into one document with data.

MongoDB aggregation query

db.collection.aggregate([
  {
    $unwind: "$data"
  },
  {
    $group: {
      _id: {
        sellerId: "$data.sellerId"
      },
      "sumPrice": {
        $sum: "$data.price"
      }
    }
  },
  {
    "$group": {
      "_id": 0,
      "data": {
        $addToSet: {
          "sellerId": "$_id.sellerId",
          "sumPrice": "$sumPrice"
        }
      }
    }
  }
])

Sample Mongo Playground

Output

[
  {
    "_id": 0,
    "data": [
      {
        "sellerId": ObjectId("61307abca667678553be81cb"),
        "sumPrice": 4988
      }
    ]
  }
]

If you want to re-write the query, here are the query with sample input.

Input

[
  {
    items: [
      {
        _id: ObjectId("61435b3c0f773abaf77a367e"),
        price: 3000,
        type: "service",
        sellerId: ObjectId("61307abca667678553be81cb"),
        
      },
      {
        _id: ObjectId("613115808330be818abaa613"),
        price: 788,
        type: "product",
        sellerId: ObjectId("61307abca667678553be81cb"),
        
      },
      {
        _id: ObjectId("61307c1ea667676078be81cc"),
        price: 1200,
        type: "product",
        sellerId: ObjectId("61307abca667678553be81cb"),
        
      }
    ]
  }
]

Mongo aggregation query

db.collection.aggregate([
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: {
        sellerId: "$items.sellerId"
      },
      "sumPrice": {
        $sum: "$items.price"
      }
    }
  },
  {
    "$group": {
      "_id": 0,
      "data": {
        $addToSet: {
          "sellerId": "$_id.sellerId",
          "sumPrice": "$sumPrice"
        }
      }
    }
  }
])

Sample 2 on Mongo Playground

Output

[
  {
    "_id": 0,
    "data": [
      {
        "sellerId": ObjectId("61307abca667678553be81cb"),
        "sumPrice": 4988
      }
    ]
  }
]