CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Joe
Joe

Reputation: 4254

Mongodb, aggregate nr of items in $group

I'm doing some aggregation to get some statistics on a how many products there are in cateories.

After some piping I'm down to this:

[
  {
     topCategory: "Computer",
     mainCategory: "Stationary"
  },
  {
    topCategory: "Computer",
    mainCategory: "Laptop"
  },
  {
    topCategory: "Computer",
    mainCategory: "Stationary"
  },
  {
    topCategory: "Computer",
    mainCategory: "Stationary"
  },

]

Wanted output:

[
  {
    name: "Computer",
    count: 4,
    mainCategories: [
      {
         name: "Laptop",
         count: 2
      },
      {
         name: "Stationary",
         count: 2
      }
    ]
  }
]

My query so far:

let query = mongoose.model('Product').aggregate([
    {
      $match:  {
        project:  mongoose.Types.ObjectId(req.params.projectId)
      }
    },
    { $project:  {
        _id: 0,
        topCategory: '$category.top',
        mainCategory: '$category.main',
      }
    },
 ]);

I know I need to use $group combined with $sum. I tried different way's but can't get it work.

Upvotes: 2

Views: 58

Answers (1)

Neo-coder
Neo-coder

Reputation: 7840

In this case first you should group by mainCategory and then group by topCategory as below 

db.collection.aggregate({
  $group: {
    _id: "$mainCategory",
    "count": {
      $sum: 1
    },
    "data": {
      "$push": "$$ROOT"
    }
  }
}, {
  "$unwind": "$data"
}, {
  "$group": {
    "_id": "$data.topCategory",
    "count": {
      "$sum": 1
    },
    "mainCategories": {
      "$addToSet": {
    "name": "$_id",
    "count": "$count"
      }
    }
  }
}).pretty()

Upvotes: 2

Related Questions