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

Question

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?

Can you add sample object from productsCollection?

Jits
– Jits

2019-07-29 09:21:49 +00:00
Commented Jul 29, 2019 at 9:21 — Jits
– Jits, Commented Jul 29, 2019 at 9:21
I did so and also simplified the output

Joshua Fox
– Joshua Fox

2019-07-29 09:32:26 +00:00
Commented Jul 29, 2019 at 9:32 — Joshua Fox
– Joshua Fox, Commented Jul 29, 2019 at 9:32

Alex Blex · Accepted Answer · 2019-07-29 09:45:01Z

1

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.

answered Jul 29, 2019 at 9:45

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related