Questions are

How many products have a discount on them?

How many "Water" products don't have any discount on them?

How many products have "mega" in their name?

How many products have discount % greater than 30%?

Which brand in "Oil" section is selling the most number of products?

data format

{"_id":{"$oid":"62363ce631312ffd2dc724f5"},
{"Title":"Fortune mega"},
{"Brand":"Gewti Laurent}"
{"Name":"lorem ipsum"},
{"Original_Price":590},
{"Sale_price":590},
{"Product_category":"Oil"}

{"_id":{"$oid":"62363ce631342ffd2dc724f5"},
{"Title":"katy mega"},
{"Brand":"Gewti Laurent},
{"Name":"targi"},
{"Original_Price":1890},
{"Sale_price":1890},
{"Product_category":"Oil"}

{"_id":{"$oid":"62363ce641312ffd2dc724f5"},
{"Title":"ydnsu"},
{"Brand":"Gewti Laurent},
{"Name":"otgu"},
{"Original_Price":1390},
{"Sale_price":1290},
{"Product_category":"Water"}

{"_id":{"$oid":"62363ce431312ffd2dc724f5"},
{"Title":"ykjssu"},
{"Brand":"Gewti Laurent},
{"Name":"itru"},
{"Original_Price":190},
{"Sale_price":170},
{"Product_category":"Water"}

CodePudding user response：

Here is using aggregation pipeline:

1. Products having discount:

db.collection.aggregate([
  {
    "$match": {
      Product_category: "Oil"
    }
  }
])

2. "Water" products not having any discount:

db.collection.aggregate([
  {
    "$match": {
      "Product_category": "Water",
      "$expr": {
        "$gte": [
          "$Sale_Price",
          "$Original_Price"
        ]
      }
    }
  }
])

Note: If you want the opposite, i.e., find Water Products having discount simply change $gte to $lte

3. Products having "mega" in their name:

db.collection.find({
  "Title": {
    "$regex": "mega"
  }
})

Note: This isn't aggregation. This query can be pulled off directly.

4. Products having discount % greater than 30:

db.collection.aggregate([
  {
    "$addFields": {
      "discount_in_percent": {
        "$multiply": [
          {
            "$divide": [
              {
                "$subtract": [
                  "$Original_Price",
                  "$Sale_price"
                ]
              },
              "$Original_Price"
            ]
          },
          100
        ],
        
      }
    }
  },
  {
    "$match": {
      "Product_category": "Water",
      "$expr": {
        "$gte": [
          "$discount_in_percent",
          30
        ]
      }
    }
  }
])

Note: This is bit an overkill but it will let you know the percentage of discount on a product.

Your last question to find most selling product under a particular category is vague as pointed by @Rohit Roa. But from the given data it can the a product having the highest discount. For that you can play around a bit with second query by tweaking the $expr selector and using $project operator to get a particular product.

Since your question is more about how many rather than which you can look up on $size to get the count or loop the result.

CodePudding user response：

1. How many products have a discount on them ?

db.products.find().toArray().filter(product => product.Sale_price < product.Original_Price).length

find() returns all products in the database, toArray() converts into an array on which you can use other functions. filter() then says look at every product in the list and return a new list of products where the Sale_price is lower than the Original_Price and .length just measures the length of that list.

2. How many "Water" products don't have any discount on them?

db.products.find({"Product_category": "Water"}).toArray().filter(product => product.Sale_price >= product.Original_Price).length

Similar to first one, except now that find() accepts a filter to filter by product category and filter() accepts a different filter.

3. How many products have "mega" in their name ?

db.products.find({"Name": {$regex: /mega/}}).toArray().length

Here use a regular expression to search for words containing mega

4. How many products have discount % greater than 30% ?

db.products.find().toArray().filter(product => (product.Original_Price-product.Sale_price) > 0.3*product.Original_Price).length

Similar to earlier, just change the filter.

5. Need more input to figure this out.

These commands will work in Mongo Shell. Refer to the documentation if you need to query the database via node or python using similar functions