MongoDB - Nested aggregation with sort and limit at each stage

Question

My document looks like this:

{
  Region: "Europe",
  Country: "Luxembourg",
  Category: "Snacks",
  Sales Channel: "Offline",
  Order Priority: "C",
  Units Sold: 9357,
  Unit Price: 421.89,
  Unit Cost: 364.69,
  Total Revenue: 3947624.73,
  Total Cost: 3412404.33,
  Total Profit: 535220.4,
}

Basically, there are multiple countries with the same Region and multiple categories with the same country.

I want to run an aggregation query which returns top n Regions with their top x Countries with top y categories by Revenue. So the response should look something like this or similar:

{
  "Region": "Asia",
  "Total": 12345,
  "Countries": [{
    "country": "China",
    "Total Revenue": 1234,
    "Categories": {
      "Snacks": {
        "Revenue": 123
      },
      "Cosmetics": {
        "Revenue": 123
      }
    }
  }, {
    "country": "China",
    "Total": 1234,
    "Categories": {
      "Snacks": {
        "Revenue": 123
      },
      "Cosmetics": {
        "Revenue": 123
      }
    }
  }]
}

So far I've managed this (it returns countries' total revenue grouped by region) but I have no idea how to further divide it by categories. How do I achieve the required result?

[{
    "$group": {
      "_id": {
        "region": "$Region",
        "country": "$Country",
        "type": "$Category"
      },
      "Revenue": {
        "$sum": "$Total Revenue"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.region",
      "Revenue": {
        "$sum": "$Revenue"
      },
      "values": {
        "$push": {
          "country": "$_id.country",
          "Revenue": {
            "$sum": "$Revenue"
          }
        }
      }
    }
  },
  {
    "$project": {
      "values": {
        "$slice": [
          "$values",
          5
        ]
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  },
  {
    "$limit": 2
  }
]

The response for the above query looks like this:

{
  "_id": "Asia",
  "Countries": [{
      "country": "Sri Lanka",
      "Revenue": 731282793.7
    },
    {
      "country": "Vietnam",
      "Revenue": 634208761.24
    }
  ]
}

Yong Shun · Accepted Answer · 2022-09-11 11:24:10Z

1

You should have 3 $group stages. You miss out on the $group (2nd) stage for group by Region and Country.

{
  "$group": {
    "_id": {
      "region": "$_id.region",
      "country": "$_id.country"
    },
    "Categories": {
      $push: {
        k: "$_id.type",
        v: "$Revenue"
      }
    },
    "TotalRevenue": {
      "$sum": "$Revenue"
    }
  }
}

Complete query:

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "region": "$Region",
        "country": "$Country",
        "type": "$Category"
      },
      "Revenue": {
        "$sum": "$Total Revenue"
      }
    }
  },
  {
    "$group": {
      "_id": {
        "region": "$_id.region",
        "country": "$_id.country"
      },
      "Categories": {
        $push: {
          k: "$_id.type",
          v: "$Revenue"
        }
      },
      "TotalRevenue": {
        "$sum": "$Revenue"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.region",
      "Revenue": {
        "$sum": "$TotalRevenue"
      },
      "Countries": {
        "$push": {
          "country": "$_id.country",
          "TotalRevenue": "$TotalRevenue",
          "Categories": {
            $arrayToObject: "$Categories"
          }
        }
      }
    }
  },
  {
    "$project": {
      "Countries": {
        "$slice": [
          "$Countries",
          5
        ]
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  },
  {
    "$limit": 2
  }
])

Demo @ Mongo Playground

answered Sep 11, 2022 at 11:24

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sandeep.kgp Over a year ago

Hey, thanks for the response, it works...could you also tell how to limit number of countries and number of categories sorted by revenue in this case ?

sandeep.kgp Over a year ago

updated playground with multiple docs: mongoplayground.net/p/rwyBEWw_EjZ

Yong Shun Over a year ago

You need to perform sort in first stage, sort by "Total Revenue" descending, "Country" and "Region", and perform nested slice for categories. Demo

sandeep.kgp Over a year ago

Alright, I'll try to dive deep into this. Thanks for the help.

Collectives™ on Stack Overflow

MongoDB - Nested aggregation with sort and limit at each stage

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related