CODEWITHSUNDEEP
mongodb
Simon
Simon

Reputation: 2423

Mongodb Aggregation : select the record of every type that has the highest value in a field

Using the Mongodb Aggregation pipeline (which I am still discovering), how can I select the record of every type that has the highest value in a field.

The result should include all fields (variable and not predefined) of the matched rows.

Example data:

[
    {_id:1, "type":"a", "price":1, ...},
    {_id:2, "type":"a", "price":2, ...},
    {_id:3, "type":"b", "price":3, ...},
    {_id:4, "type":"b", "price":4, ...},
]

Expected result:

[
    {_id:2, "type":"a", "price":2, ...},
    {_id:4, "type":"b", "price":4, ...},
]

I thought about

  1. use $sort then a $limit of 1. But how to apply the limit to each type and not the entire set ?
  2. use a $match with some sort of "where max" condition if it exists ?
  3. use $group with $max or $first but this does not include all fields

How to order or nest the operations to acheive the desired result ?

Upvotes: 1

Views: 84

Answers (1)

cbartosiak
cbartosiak

Reputation: 785

Try the query below:

db.yourDb.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $sort: {
                "price": -1
            }
        },

        // Stage 2
        {
            $group: {
                "_id": "$type",
                "document": { "$first": "$$ROOT" }
            }
        },

        // Stage 3
        {
            $replaceRoot: {
                "newRoot": "$document"
            }
        },

    ]

);

It should return the following results:

{ 
    "_id" : NumberInt(2), 
    "type" : "a", 
    "price" : NumberInt(2)
}
{ 
    "_id" : NumberInt(4), 
    "type" : "b", 
    "price" : NumberInt(4)
}

Upvotes: 2

Related Questions