CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-frameworkaggregate
Mohammad Shehab
Mohammad Shehab

Reputation: 35

MongoDB Aggregate $group for two accumulated fields

I have the following documents

{ 
   id: ObjectId,
   branch: ObjectId,
   name: "lorem ipsum",
   type: "Delivery",
   total: 10.5
},
{ 
   id: ObjectId,
   branch: ObjectId,
   name: "lorem ipsum 22",
   type: "Carry out",
   total: 7.5
}, 
.
.
.

I'm trying to calculate the average total for each branch per order type
expected result for each branch like this

{
   branch: ObjectId,
   name: "lorem ipsum",
   deliveryAVG: 8.7,
   CarryOut: 6.9
}

Any suggestions ?

Upvotes: 1

Views: 140

Answers (1)

mickl
mickl

Reputation: 49985

You can use $group twice to aggregate by branch and type and then use $arrayToObject to turn each type into new object keys:

db.collection.aggregate([
    {
        $group: {
            _id: {
                branch: "$branch",
                type: "$type"
            },
            avg: { $avg: "$total" },
            name: { $first: "$name" }
        }
    },
    {
        $group: {
            _id: "$_id.branch",
            name: { $first: "$name" },
            averages: {
                $push: {
                    k: "$_id.type",
                    v: "$avg"
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $arrayToObject: {
                    $concatArrays: [ "$averages", [ { "k": "_id", "v": "$_id" }, { "k": "name", "v": "$name" } ] ]
                }
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions