CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Joshua Fox
Joshua Fox

Reputation: 19655

How do I return group results by multiple fields in MongoDB?

In MongoDB, the aggregate query below gives the average price per product-category, where business-name is "Foobar Inc".

var match = { $match: {"businessName": "FooBar Inc"}, ;

var group = { $group: { _id: "$productCategory", total: { $avg: "$price" }}}

var results = await db.aggregate("productsCollection", limit, match, group);

Example object from productCollection:

{"businessName": "FooBar Inc.",  "productCategory": "toys", "price":88}

Output:

 [{"_id": "shoes", "total":97}, {"_id": "toys", "total":77}]

However, I want to replace "FooBar Inc." with a variable, so that multiple average-prices are returned.

The data returned would be something like:

shoes, Foobar Inc.: average price 97

toys, Foobar Inc.: average price 88

shoes, Quux Ltd.: average price 68

toys, Quux Ltd.: average price 101

I could run multiple aggregate queries, but is there away to do this in a single query?

Upvotes: 0

Views: 52

Answers (1)

Alex Blex
Alex Blex

Reputation: 37038

You need to group by both productCategory and businessName:

var group = { $group: { 
    _id: {category: "$productCategory", name: "$businessName"}, 
    total: { $avg: "$price" }
}}

and no $match stage of course.

Upvotes: 1

Related Questions