MongoDB : Select top N records based on group by clause

user_8726081 0
Viewed 260

Below is my sample collection :- 

{
"_id" : "fffdb22b-912d-4166-909b-7d0fe790ba2a", 
"companyVendorId" : "1001", 
"date" : ISODate("2019-01-02T05:30:00.000+05:30"),  
"amount" : 100  
},
{
"_id" : "fff94874-27af-4a39-ae59-3a46b8c7f573", 
"companyVendorId" : "1002", 
"date" : ISODate("2019-01-25T05:30:00.000+05:30"),  
"amount" : 200  
 },
{
"_id" : "fff94874-27af-4a39-ae59-3a46b8c7f573", 
"companyVendorId" : "1002", 
"date" : ISODate("2019-01-29T05:30:00.000+05:30"),  
"amount" : 200  
 },

{
"_id" : "fff68faf-2f11-480f-83d2-bfcb45b12d5b", 
"companyVendorId" : "1004", 
"date" : ISODate("2019-01-12T05:30:00.000+05:30"),  
"amount" : 500

 },

{
"_id" : "fff4dfaa-46cd-48e3-a871-1f086a2c5438", 
"companyVendorId" : "1005", 
"date" : ISODate("2019-02-13T05:30:00.000+05:30"),  
"amount" :600

 },

 {
"_id" : "fff18ff2-015e-4ddc-81f2-a12ab3503d05", 
"companyVendorId" : "1006", 
"date" : ISODate("2019-02-08T05:30:00.000+05:30"),  
"amount" : 700

},
{
"_id" : "ffeb16cd-ae1b-4c1e-aa93-d64347b5ff38", 
"companyVendorId" : "1007", 
"date" : ISODate("2019-02-18T05:30:00.000+05:30"),  
"amount" :800
}

Requirement :- I need top 2 vendors for each month (for current year) based on their amount, if more than one transactions are there for same "companyVendorId" in one month then we need to show sum of amount.

Expected result :- 

{
"month":1
"companyVendorId" : "1004",     
"totalAmount" : 500 
 },
{
 "month":1
"companyVendorId" : "1002",     
"totalAmount" : 400

 },
{
"month":2
"companyVendorId" : "1007",         
"totalAmount" :800
 },

{
"month":2   
"companyVendorId" : "1006",     
"totalAmount" : 700

}

So far i am in between trying to make query, below query i am able to make :- 

 db.getCollection('transaction').aggregate([
 {
"$project":
 {
 "amount": 1,
 "companyVendorId":1,

 "month": { "$month": "$date" }, "year": { "$year": "$date" }

 }
 },
 {
 "$match":
   {
    "year": 2020
   }

  }, {
 "$group": {
    "_id": {
    "month": "$month",
   "companyVendorId":"$companyVendorId"
    },
   "totalAmount": { "$sum": "$amount" }
     }
     }
    ])

This query giving me result based on my requirement but not able to select top 2 vendors.

aggregation-framework mongodb
2 Answers

A bit complicated. Stages 7 and 8 are the most complicated part, since you need to sum vendors with the same ID. If top 2...n vendors are the same, we need to sum them all and count it as vendor Nº1. Same process for the vendor Nº2.

Explanation

  1. Stage 1. If you want to apply MongoDB indexes, we can filter records by date field. If no indexes, you can replace your 2 stages with mine.
  2. Stage 2. We group by month and create two arrays with companyVendorId and amount values.
  3. Stages 3-4. We flatten data2 array to calculate $max / $min amounts per companyVendorId. This would help us to sum top vendors with the same companyVendorId.
  4. Stages 5-6. Now we order vendors with the highest amount and define list of unique vendors with $max / $min amounts.

  5. Stage 7. Here we fix vendors min amount. Since $group takes the smallest value, we replace it with next vendors max value:
    {companyVendorId:1, max:500, min:50}, ---\ {companyVendorId:1, max:500, min:200}, {companyVendorId:2, max:400, min:200} ---/ {companyVendorId:2, max:400, min:200}

  6. Stage 8. Now we sum vendors with the same companyVendorId and amount between min and max values.

  7. Stages 9-10-11. We flatten previous result, transform into desired out and sort documents.
db.transaction.aggregate([
  {
    "$match": {
      "date": {
        "$gte": ISODate("2019-01-01T00:00:00Z"),
        "$lte": ISODate("2019-12-31T23:59:59Z")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "$month": "$date"
      },
      "data": {
        "$push": {
          "_id": {
            "$month": "$date"
          },
          "companyVendorId": "$companyVendorId",
          "amount": "$amount"
        }
      },
      "data2": {
        "$push": {
          "companyVendorId": "$companyVendorId",
          "amount": "$amount"
        }
      }
    }
  },
  {
    "$unwind": "$data2"
  },
  {
    "$group": {
      "_id": {
        "month": "$_id",
        "companyVendorId": "$data2.companyVendorId"
      },
      "data": {
        "$first": "$data"
      },
      "max": {
        "$max": "$data2.amount"
      },
      "min": {
        "$min": "$data2.amount"
      }
    }
  },
  {
    "$sort": {
      "_id.month": 1,
      "max": -1
    }
  },
  {
    "$group": {
      "_id": "$_id.month",
      "data": {
        "$first": "$data"
      },
      "data2": {
        "$push": {
          "companyVendorId": "$_id.companyVendorId",
          "max": "$max",
          "min": "$min"
        }
      }
    }
  },
  {
    $addFields: {
      data2: {
        $map: {
          input: {
            $range: [
              0,
              {
                $min: [
                  {
                    $size: "$data2"
                  },
                  2
                ]
              },
              1
            ]
          },
          as: "i",
          in: {
            companyVendorId: {
              $arrayElemAt: [
                "$data2.companyVendorId",
                "$$i"
              ]
            },
            max: {
              $arrayElemAt: [
                "$data2.max",
                "$$i"
              ]
            },
            min: {
              $ifNull: [
                {
                  $arrayElemAt: [
                    "$data2.max",
                    {
                      $add: [
                        "$$i",
                        1
                      ]
                    }
                  ]
                },
                {
                  $arrayElemAt: [
                    "$data2.min",
                    "$$i"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      data3: {
        $map: {
          input: {
            $slice: [
              "$data2",
              2
            ]
          },
          as: "topN",
          in: {
            $reduce: {
              input: "$data",
              initialValue: {
                _id: "",
                companyVendorId: "$$topN.companyVendorId",
                amount: 0
              },
              in: {
                _id: "$_id",
                companyVendorId: "$$value.companyVendorId",
                amount: {
                  $add: [
                    "$$value.amount",
                    {
                      $cond: [
                        {
                          $and: [
                            {
                              $eq: [
                                "$$value.companyVendorId",
                                "$$this.companyVendorId"
                              ]
                            },
                            {
                              $gte: [
                                "$$this.amount",
                                "$$topN.min"
                              ]
                            },
                            {
                              $lte: [
                                "$$this.amount",
                                "$$topN.max"
                              ]
                            }
                          ]
                        },
                        "$$this.amount",
                        0
                      ]
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$data3"
  },
  {
    $replaceWith: "$data3"
  },
  {
    $sort: {
      _id: 1,
      amount: -1
    }
  }
])

MongoPlayground

Note: You need MongoDB v4.2. For earlier versions (>v3.4), replace:

{ $replaceWith: "$data3" }
         with 
{ $replaceRoot: {newRoot: "$data3" }}
user_37104900

One option to do it is:

  1. Match last year documents and add the month
  2. $group by month and vendor to get the vendor's amount per month
  3. $sort to get them in order
  4. $group again but by month, to get the vendors sorted per each month
  5. keep only top 2 using $slice
  6. format the results
db.collection.aggregate([
 {
    $match: {
      date: {
        $gte: ISODate("2019-01-01T00:00:00Z"),
        $lte: ISODate("2019-12-31T23:59:59Z")
      }
    }
  },
  {$addFields: {month: {$month: "$date"}}},
  {
    $group: {
      _id: {vendor: "$companyVendorId", month: "$month"},
      amount: {$sum: "$amount"}
    }
  },
  {$sort: {amount: -1}},
  {
    $group: {
      _id: "$_id.month",
      top: {
        $push: {
          companyVendorId: "$_id.vendor",
          amount: "$amount",
          month: "$_id.month"
        }
      }
    }
  },
  {$project: {top: {$slice: ["$top", 2]}}},
  {$unwind: "$top"},
  {$replaceRoot: {newRoot: "$top"}},
  {$sort: {month: 1, amount: -1}}
])

Playground

user_184823100
Related