mongodb group values by three fields

AD G 0
Viewed 13

For example, I have these documents:

[
  {
    "state": "Jharkhand",
    "district": "Ranchi",
    "salesman": "amit"
  },
  {
    "state": "Jharkhand",
    "district": "Ranchi",
    "salesman": "sumit"
  },
  {
    "state": "Jharkhand",
    "district": "Bokaro",
    "salesman": "rahul"
  },
  {
    "state": "Jharkhand",
    "district": "Bokaro",
    "salesman": "rajesh"
  },
  {
    "state": "Jharkhand",
    "district": "Bokaro",
    "salesman": "ravi"
  },
  {
    "state": "Orisha",
    "district": "Puri",
    "salesman": "anish"
  },
  {
    "state": "Orisha",
    "district": "Puri",
    "salesman": "arjun"
  },
  {
    "state": "Orisha",
    "district": "Puri",
    "salesman": "aryan"
  },
  {
    "state": "Orisha",
    "district": "Bhubneswar",
    "salesman": "anuj"
  },
  {
    "state": "Orisha",
    "district": "Bhubneswar",
    "salesman": "chandan"
  },
  {
    "state": "Orisha",
    "district": "Bhubneswar",
    "salesman": "charan"
  }
]

and so on.

How can I make a request, which will describe the N state, N district and N salesman?

Example of expected result:


    {
    "_id" : "state",
    "districts" : [
        {
            "district" : "Ranchi",
            "salesmen" : [
                {
                    "salesmen" : "amit",
                    "count" : 1
                },
            {
                "salesmen" : "sumit",
                    "count" : 1
                }
            ],
            "count" : 2
        }
    ],
    "total" : 2
}
aggregation-framework mongodb
1 Answers

You just need to execute $group three times, like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        state: "$state",
        district: "$district",
        salesman: "$salesman"
      },
      sum: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: {
        state: "$_id.state",
        district: "$_id.district",
        
      },
      salesmen: {
        $push: {
          salesmen: "$_id.salesman",
          count: "$sum"
        }
      },
      count: {
        $sum: "$sum"
      }
    }
  },
  {
    $group: {
      _id: "$_id.state",
      districts: {
        $push: {
          district: "$_id.district",
          salesmen: "$salesmen",
          count: "$count"
        }
      },
      count: {
        $sum: "$count"
      }
    }
  }
])

Mongo Playground

Tom Slabbaert0
Related