CODEWITHSUNDEEP
mongodbmongoosemongodb-query
Lam Lee
Lam Lee

Reputation: 179

MongoDB - How to query with a field with calculation

I have products like this:

[
  {
    "_id": 1,
    "name": "Apple",
    "price": 3,
    "stock": 5
  },
  {
    "_id": 2,
    "name": "Banana",
    "price": 1,
    "stock": 6
  }
]

How to query and filter the products that have the total price highest? (total price of a product = price * stock)

Thanks for help!

Upvotes: 0

Views: 70

Answers (1)

Yong Shun
Yong Shun

Reputation: 51195

An aggregate query to work with:

  1. $set - Set totalPrice field by multiplying both price and stock fields.
  2. $sort - Order by totalPrice descending.
  3. $limit - Take only one document.
db.collection.aggregate([
  {
    $set: {
      totalPrice: {
        "$multiply": [
          "$price",
          "$stock"
        ]
      }
    }
  },
  {
    $sort: {
      totalPrice: -1
    }
  },
  {
    $limit: 1
  }
])

Note: Although the above query gets the document with the highest total price, it gets the first and only one document.

Sample Mongo Playground

For the above scenario mentioned that possible there are multiple documents with the highest total price, so we need a ranking.

  1. $set - Set totalPrice field by multiplying both price and stock fields.
  2. $rank - Rank the documents by sorting with totalPrice descending.
  3. $match - Filter the documents with rank: 1.
db.collection.aggregate([
  {
    $set: {
      totalPrice: {
        "$multiply": [
          "$price",
          "$stock"
        ]
      }
    }
  },
  {
    $setWindowFields: {
      sortBy: {
        totalPrice: -1
      },
      output: {
        rank: {
          $rank: {}
        }
      }
    }
  },
  {
    $match: {
      rank: 1
    }
  }
])

Sample Mongo Playground ($rank)

Upvotes: 1

Related Questions