MongoDB - Obtain full document of a group taking into account the minimum value of one property

Question

Good afternoon, I'm starting in MongoDB and I have a doubt with the group aggregation.

From the following set of documents; I need to get the cheapest room of all similar (grouping by identifier room).

{"_id":"874521035","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"1"},"name":"Doble"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"fare":{"id":"NRF","name":"No reembolsable"},"price":{"cost":{"$numberInt":"115"},"net":{"$numberInt":"116"},"pvp":{"$numberInt":"126"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}
{"_id":"123456789","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"1"},"name":"Doble"},"board":{"id":{"$numberInt":"2"},"name":"Alojamiento y desayuno"},"fare":{"id":"NOR","name":"Reembolsable"},"price":{"cost":{"$numberInt":"120"},"net":{"$numberInt":"121"},"pvp":{"$numberInt":"131"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}
{"_id":"987654321","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"2"},"name":"Triple"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"fare":{"id":"NOR","name":"Reembolsable"},"price":{"cost":{"$numberInt":"125"},"net":{"$numberInt":"126"},"pvp":{"$numberInt":"136"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}
{"_id":"852963147","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"3"},"name":"Doble uso individual"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"price":{"cost":{"$numberInt":"99"},"net":{"$numberInt":"100"},"pvp":{"$numberInt":"110"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}

I've got obtain only the cheapest price, the room identifier and the number of repetitions.

db.consolidation.aggregate ([
{
    $group: {
        _id: "$ room.id",
        "cheapest": {$ min: "$ price.pvp"},
        "qty": {$ sum: 1}
    }
}]);

{"_id": 2, "cheapest": 136, "qty": 1}
{"_id": 3, "cheapest": 110, "qty": 1}
{"_id": 1, "cheapest": 126, "qty": 2}

Investigating I have seen that data can be obtained with $first or $last, but the data is not the data I need since it is obtained according to the position of the document.

What I need is to obtain from the set of documents, each document with the cheapest room. This is the result I expect:

{"_id":"874521035","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"1"},"name":"Doble"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"fare":{"id":"NRF","name":"No reembolsable"},"price":{"cost":{"$numberInt":"115"},"net":{"$numberInt":"116"},"pvp":{"$numberInt":"126"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}
{"_id":"987654321","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"2"},"name":"Triple"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"fare":{"id":"NOR","name":"Reembolsable"},"price":{"cost":{"$numberInt":"125"},"net":{"$numberInt":"126"},"pvp":{"$numberInt":"136"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}
{"_id":"852963147","provider":{"id":{"$numberInt":"2"},"name":"HotelBeds"},"accommodation":{"id":{"$numberInt":"36880"},"name":"Hotel Goya"},"room":{"id":{"$numberInt":"3"},"name":"Doble uso individual"},"board":{"id":{"$numberInt":"1"},"name":"Sólo alojamiento"},"price":{"cost":{"$numberInt":"99"},"net":{"$numberInt":"100"},"pvp":{"$numberInt":"110"}},"fees":{"agency":{"$numberInt":"10"},"cdv":{"$numberInt":"1"}},"cancellation-deadeline":"2019-12-31","payment-deadeline":"2019-12-30"}

I hope I have explained.

Thanks in advance.

Regards.

Answer 1

You can add capture $$ROOT as part of your $group stage and then use $filter to compare a list of your rooms against min value. $replaceRoot will allow you to get original shape:

db.collection.aggregate([
    {
        $group: {
            _id: "$room.id",
            "cheapest": {
                $min: "$price.pvp"
            },
            "qty": { $sum: 1 },
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayElemAt: [ { $filter: { input: "$docs", cond: { $eq: [ "$$this.price.pvp", "$cheapest" ] } } }, 0 ] }
        }
    }
])

Mongo Playground