CODEWITHSUNDEEP
mongodbmongodb-query
j3d
j3d

Reputation: 9734

MongoDB: How to get the sub-document that contains the lowest value

Given the following document...

{ "_id": ObjectId("56116d8e4a0000c9006b57ac"), "name": "Stock 1", "items" [
    { "price": 1.50, "desc": "Item 1" }
    { "price": 1.70, "desc": "Item 2" }
    { "price": 1.10, "desc": "Item 3" }
  ]
}

I want to get the item with the lowest price. Here below is my attempt to sort prices from lowest to highest:

db.stock.wip.aggregate([
  {$unwind: "$items"}, 
  {$sort: {"items.price":1}}, 
  {$group: {_id:"$_id", items: {$push:"$items"}}}
]);

... and here is the result:

"result" : [
  {
    "_id" : ObjectId("56116d8e4a0000c9006b57ac"),
    "items" : [
      {
        "price" : 1.10,
        "desc" : "Item 3"
      },
      {
        "price" : 1.50,
        "desc" : "Item 1"
      },
      {
        "price" : 1.70,
        "desc" : "Item 2"
      }
    ]
  }
],
"ok" : 1

How do I just get Item 3 (the cheapest)? This is the result I'm looking for:

{
  "price" : 1.10,
  "desc" : "Item 3"
}

Upvotes: 0

Views: 139

Answers (2)

Stefano Castriotta
Stefano Castriotta

Reputation: 2923

Use the $first operator instead of $push, then do a $project:

db.stock.wip.aggregate([
  {$unwind: "$items"}, 
  {$sort: {"items.price":1}},
  {$group: {_id:"$_id", items: {$first:"$items"}}},
  { $project: {
    _id: 0,
    price: "$items.price",
    desc: "$items.desc",
  }}
]);

If you want to limit results only to the first item on the collection, add a {$limit:1} operator after the $sort:

db.stock.wip.aggregate([
  {$unwind: "$items"}, 
  {$sort: {"items.price":1}},
  {$limit:1},
  {$group: {_id:"$_id", items: {$first:"$items"}}},
  { $project: {
    _id: 0,
    price: "$items.price",
    desc: "$items.desc",
  }}
]);

Upvotes: 1

vladzam
vladzam

Reputation: 5918

I managed to write an aggregation query starting from your own, by slightly changed the $group stage and adding an additional $project step:

db.stock.wip.aggregate([
  {$unwind: "$items"}, 
  {$sort: {"items.price":1}}, 
  {$group: {
    _id:null, 
    lowestItem: {$first:"$items"}}},
  {$project: {_id: 0, price: "$lowestItem.price", desc: "$lowestItem.desc"}}
]);

Upvotes: 1

Related Questions