MongoDB group by and SUM by array-CodePudding

I'm new in mongoDB.

This is one example of record from collection:

  {
    supplier: 1,
    type: "sale",
    items: [
      {
        "_id": ObjectId("60ee82dd2131c5032342070f"),
        "itemBuySum": 10
      },
      {
        "_id": ObjectId("60ee82dd2131c50323420710"),
        "itemBuySum": 10,
        
      },
      {
        "_id": ObjectId("60ee82dd2131c50323420713"),
        "itemBuySum": 10
      },
      {
        "_id": ObjectId("60ee82dd2131c50323420714"),
        "itemBuySum": 20
      }
    ]
  }

I need to group by TYPE field and get the SUM. This is output I need:

{
    supplier: 1,
    sales: 90,
    returns: 170
}

please check Mongo playground for better understand. Thank you!

CodePudding user response：

$match - Filter documents.
$group - Group by type and add item into data array which leads to the result like:

[
  [/* data 1 */], 
  [/* data 2 */]
]

$project - Decorate output document.

3.1. First $reduce is used to flatten the nested array to a single array (from Result (2)) via $concatArrays.

3.2. Second $reduce is used to aggregate $sum the itemBuySum.

db.collection.aggregate({
  $match: {
    supplier: 1
  },
  
},
{
  "$group": {
    "_id": "$type",
    "supplier": {
      $first: "$supplier"
    },
    "data": {
      "$push": "$items"
    }
  }
},
{
  "$project": {
    _id: 0,
    "supplier": "$supplier",
    "type": "$_id",
    "returns": {
      "$reduce": {
        "input": {
          "$reduce": {
            input: "$data",
            initialValue: [],
            in: {
              "$concatArrays": [
                "$$value",
                "$$this"
              ]
            }
          }
        },
        "initialValue": 0,
        "in": {
          $sum: [
            "$$value",
            "$$this.itemBuySum"
          ]
        }
      }
    }
  }
})

Sample Mongo Playground