How to use $bucketAuto where the resulting buckets also needs to be grouped by another field

[t_s]

I need to create an aggregation pipeline that return price ranges for each product category.

What I need to avoid is to load all available categories and call the Database again, one by one with a $match on each category. There must be a better way to do it.

Product documents

{
  Price: 500,
  Category: 'A'
},
{
  Price: 7500,
  Category: 'A'
},
{
  Price: 340,
  Category: 'B'
},
{
  Price: 60,
  Category: 'B'
}

Now I could use a $group stage to group the prices into an array by their category.

{
  _id: "$Category",
  Prices: {
    $addToSet: "$Price"
  }
}

Which would result in

{
  _id: 'A',
  Prices: [500, 7500]
},
{
  _id: 'B',
  Prices: [340, 60]
}

But If I use $bucketAuto stage after this, I am unable to groupBy multiple properties. Meaning it would not take the categories into account.

I have tried the following

{
  groupBy: "$Prices",
  buckets: 5,
  output: {
    Count: { $sum: 1}
  }
}

This does not take categories into account, but I need the generated buckets to be organised by category. Either having the category field within the _id as well or have it as another field and have 5 buckets for each distinct category:

{
  _id: {min: 500, max: 7500, category: 'A'},
  Count: 2
},
{
  _id: {min: 60, max: 340, category: 'B'},
  Count: 2
}...

[Kushagra_Kesav]

Hi @t_s,

Welcome to the MongoDB Community forums

You could use the output document to get the category field added to the final output result not within the _id because it just takes one field to group the documents.

Here is the aggregation pipeline:

  db.product.aggregate( [
    {
      $bucketAuto: {
          groupBy: "$price",
          buckets: 5,
          output: {
            Count: { $sum: 1},
            "category": { $first: "$category" }
          }
      }
    }
 ] )

Output

{ _id: { min: '300', max: '500' }, Count: 1, category: 'A' }
{ _id: { min: '500', max: '5000' }, Count: 1, category: 'B' }
{ _id: { min: '5000', max: '7500' }, Count: 1, category: 'B' }
{ _id: { min: '7500', max: '7500' }, Count: 1, category: 'A' }

For more information around $bucket and $bucketAuto, please refer to the official docs.

I hope it answers your questions. Please let us know if you have any follow-up questions.

Thanks,
Kushagra