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
}...

CodePudding user response：

Query1

• if you want to group by category and find the max and min price for that category you can do it like this

Playmongo

aggregate(
[{"$group": 
   {"_id": "$Category",
    "min-price": {"$min": "$Price"},
    "max-price": {"$max": "$Price"}}}])

Query2

• if you want to group by category and then apply the bucket inside the array of the prices, to create like 5 buckets like in your example
• you can do it with a trick, that allows us to use stage operators to do operators inside the array
• the trick is to have 1 extra collection with only 1 document [{}]
• you do lookup, you unwind that array, you do what you want on it
• here we unwind the array and do $bucketAuto on it, with 5 buckets, like in your example, this way we can have group by category, and the prices in 5 ranges (5 buckets)

Playmongo

aggregate(
[{"$group": {"_id": "$Category", "prices": {"$push": "$Price"}}},
 {"$lookup": 
   {"from": "coll_with_1_empty_doc",
    "pipeline": 
     [{"$set": {"prices": "$$prices"}}, {"$unwind": "$prices"},
       {"$bucketAuto": {"groupBy": "$prices", "buckets": 5}}],
    "as": "bucket-prices",
    "let": {"prices": "$prices", "category": "$_id"}}}])

If none of the above works, if you can give sample documents and example output