CODEWITHSUNDEEP
mongodb
Arman Ordookhani
Arman Ordookhani

Reputation: 6556

MongoDB - Group by customer and retrieve documents with maximum price

Consider having these objects in db.invoices

{ "customer" : "john", "price" : 4, "weekday": "WED" }
{ "customer" : "john", "price" : 8, "weekday": "SUN" }
{ "customer" : "john", "price" : 6, "weekday": "SAT" }
{ "customer" : "john", "price" : 5, "weekday": "SUN" }    
{ "customer" : "bob", "price" : 10, "weekday": "SAT" }
{ "customer" : "bob", "price" : 15, "weekday": "MON" }

How can I query for documents having the maximum price for each customer? For above sample:

[ {
  "customer": "bob",
  "price": 15,
  "weekday": "MON"
}, {
  "customer": "john",
  "price": 8,
  "weekday": "SUN"
} ]

I can't figure it out using aggregation framework.

Edit 1: Problem is getting weekdays along with customer names. I do not want the maximum price alone.

Upvotes: 2

Views: 1064

Answers (3)

Asya Kamsky
Asya Kamsky

Reputation: 42352

Here's a way to get the result you want, it's one of several:

db.invoices.aggregate([
    {$project: {customer: 1, other:{ price: "$price", weekday: "$weekday"}}},
    {$group: {
        _id: '$customer',
        max: {$max: '$other'}
    }
])

Upvotes: 1

JohnnyHK
JohnnyHK

Reputation: 312095

Because you want to include weekday you need to pre-sort the docs to put the doc you want from each group first and then use $group with $first:

db.invoices.aggregate([
    {$sort: {customer: 1, price: -1}},
    {$group: {
        _id: '$customer',
        price: {$first: '$price'},
        weekday: {$first: '$weekday'}
    }}
])

Upvotes: 3

user20140268
user20140268

Reputation: 1288

You can use $group operator:

db.invoises.aggregate([
  { $group : { _id: '$customer', price: { $max: '$price' } } }
])

Upvotes: 0

Related Questions