how to filter the product and optimize mongodb query?

user_14942898 0
Viewed 52

How to filter the product and optimize mongodb query,

We would like to get popular products base on some conditions i.e which products are orders, view and likes.

db.products.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "_id",
      "foreignField": "product_id",
      "as": "orders"
    }
  },
  {
    "$addFields": {
      "orderCount": {
        "$size": {
          "$cond": [
            {
              "$isArray": "$orders"
            },
            "$orders",
            []
          ]
        }
      }
    }
  },
  {
    "$addFields": {
      "likeCount": {
        "$size": {
          "$cond": [
            {
              "$isArray": "$likes"
            },
            "$likes",
            []
          ]
        }
      }
    }
  },
  {
    "$addFields": {
      "sumCount": {
        "$sum": [
          "$orderCount",
          "$likeCount",
          "$view"
        ]
      }
    }
  },
  {
    $sort: {
      "sumCount": -1
    }
  }
])

https://mongoplayground.net/p/fIG3-yHGuV6

Have to use multiple $addFields what would be best option to achieve the products that have the most orders, likes and views. please guide

thanks

aggregation-framework mongodb
2 Answers

You can also use projection to minimize the code

{
    "$project": {
      "likes": 1,
      "orderCount": {
        "$size": {
          "$cond": {
            "if": {
              "$isArray": [
                "$orders"
              ]
            },
            "then": "$orders",
            "else": []
          }
        }
      },
      "likeCount": {
        "$size": {
          "$cond": {
            "if": {
              "$isArray": [
                "$likes"
              ]
            },
            "then": "$likes",
            "else": []
          }
        }
      },
      "views": {
        "$ifNull": [
          "$view",
          0
        ]
      }
    }
  },

https://mongoplayground.net/p/qUNftLP_-PN

check the mongoplayground.

user_66067000

I would suggest 2 corrections,

  1. orders size does not need verification if it is an array or not condition because $lookup stage will always return in array

  2. You can do both operations for orderCount and likeCount in a single $addFields stage

You final query would be,

db.products.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "_id",
      "foreignField": "product_id",
      "as": "orders"
    }
  },
  {
    "$addFields": {
      "orderCount": { "$size": "$orders" },
      "likeCount": {
        "$size": {
          "$cond": [{ "$isArray": "$likes" }, "$likes", []]
        }
      }
    }
  },
  {
    "$addFields": {
      "sumCount": {
        "$sum": ["$orderCount", "$likeCount", "$view"]
      }
    }
  },
  { "$sort": { "sumCount": -1 } }
])

Playground

user_89871280
Related