CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
mbuyu
mbuyu

Reputation: 45

How can I get the lowest values in a MongoDB collection?

I have a MongoDB collection called product which has the following documents as seen below.

{
    "product" : "Milk",
    "barcode" : 12345,
    "price" : 100,
    "store" : "BestBuy"
},
{
    "product" : "Milk",
    "barcode" : 12345,
    "price" : 100,
    "store" : "WalMart"
},
{
    "product" : "Milk",
    "barcode" : 12345,
    "price" : 130,
    "store" : "Target"
},
{
    "product" : "Milk",
    "barcode" : 12345,
    "price" : 500,
    "store" : "Game"
}

I wish to query the collection and only return documents that have the lowest price e.g

{ 
    product: "Milk",
    barcode: 12345, 
    price: 100, 
    store: "BestBuy"
}
{ 
    product: "Milk",
    barcode: 12345, 
    price: 100,
    store: "WalMart"
}

But when I run my aggregation query: 

db.test.aggregate([{$match:{barcode:1234}},{$group: {_id:"$name", price:  {$min:"$price"} } }])

It only returns one document.

Upvotes: 4

Views: 2101

Answers (1)

Sede
Sede

Reputation: 61225

You need to $group your documents by "price". From there, you $sort them by "_id" in ascending order and use $limit to return the first document which nothing other than the document with the minimum value.

db.products.aggregate([ 
    { "$group": { 
        "_id": "$price", 
        "docs": { "$push": "$$ROOT" } 
    }},
    { "$sort": { "_id": 1 } }, 
    { "$limit": 1 } 
])

which produces something like:

{
    "_id" : 100,
    "docs" : [
        {
            "_id" : ObjectId("574a161b17569e552e35edb5"),
            "product" : "Milk",
            "barcode" : 12345,
            "price" : 100,
            "store" : "BestBuy"
        },
        {
            "_id" : ObjectId("574a161b17569e552e35edb6"),
            "product" : "Milk",
            "barcode" : 12345,
            "price" : 100,
            "store" : "WalMart"
        }
    ]
}

Upvotes: 2

Related Questions