Mongodb Query Aggregation and Groupby complex filter , sum , percent query

Question

I have a complex group query.

Data is as follows:

Aggregation as follows:

match by doc_id
group by name
project: name, name_count, amount, desc as { value: identifed by max sum of amount in that list of desc , count: sum of (percent*100)^2, percent:its percent considering amount in that list}
same with L1 and L2. But L1 L2 are referenced field {_id, name} from another collection. So, I need to project both _id, name and what I do in point 3 above.

Therefore after execution lets say result would be :

...
},
"_id" : {
        "name" : "abc"
    }, 
    "amount" : 45.0, 
    "count" : 4.0, 
    "desc" : {
            "value" : "Laptop",  // based on highest sum amount in group:'abc' i.e. 25.0 for laptop
            "count" : 5061.72, // (56*100)^2 + (44*100)^2
            "percent" :  25.0*100/45.0 = 56.0
        }, 
...

Test Data Link: MonogoDb Playground

Udpated: 07/11/2019

Added example for calculating count

Hope I was clear. Kindly help.

Thats just a sample. not needed otherwise. Also ,I updated the count and percent requirement. Please take a look in that — Vikash

matthPen matthPen · Accepted Answer · 2019-11-06T12:48:10

Don't understand the calculation you need for count. However, here's the query you can use to fit your need :

db.collection.aggregate([
  {
    $match: {
      "doc_id": 1
    }
  },
  {
    $group: {
      _id: {
        name: "$name",
        desc: "$desc"
      },
      amount: {
        $sum: "$amount"
      },
      count: {
        $sum: 1
      },

    }
  },
  {
    $sort: {
      "_id.name": 1,
      "amount": -1
    }
  },
  {
    $group: {
      _id: "$_id.name",
      amount: {
        $sum: "$amount"
      },
      count: {
        $sum: "$count"
      },
      desc: {
        $first: {
          value: "$_id.desc",
          descAmount: "$amount"
        }
      }
    },

  },
  {
    $addFields: {
      "desc.percent": {
        $multiply: [
          {
            $divide: [
              "$desc.descAmount",
              "$amount"
            ]
          },
          100
        ]
      }
    }
  }
])

The tip is to group twice, with a sort between, to get sub-total and first element (the one with the biggest sub-total for each name). Now you can adapt you count calculation as you need.

You can test it here.

Mongodb Query Aggregation and Groupby complex filter , sum , percent query

1 Answers