Question 115451 in Mongodb Question posted in
The official documentation can be found here.

MongoDB Aggregation: Find most popular Products filtered by Date Range

I want to find products that are most popular sort by quantity of amount sold.

I have the following data:

db={
  orders: [
    {
      "_id": "63f37d1ac3dff5dd6d8cd156",
      "cartItems": [
        {
          "qty": 1,
          "product": {
            "_id": "63515ad7d5f84ecbaac38f1e",
            "code": "1",
            "name": "Coca-Cola",
            "pricePerItem": 3,
            "group": "Milk",
            
          },
          "price": 3,
          "total": 3,
          
        }
      ],
      "createdAt": "2023-02-20T14:00:58.683Z",
      
    },
    {
      "_id": "63f37cd9c3dff5dd6d8cd14b",
      "cartItems": [
        {
          "qty": 1,
          "product": {
            "_id": "63515aeed5f84ecbaac38f24",
            "code": "2",
            "name": "Fanta",
            "pricePerItem": 4,
            "group": "Drink",
            
          },
          "price": 4,
          "total": 4,
          
        },
        {
          "qty": 2,
          "product": {
            "_id": "63515ad7d5f84ecbaac38f1e",
            "code": "1",
            "name": "Coca-Cola",
            "pricePerItem": 3,
            "group": "Milk",
            
          },
          "price": 3,
          "total": 6,
          
        }
      ],
      "createdAt": "2023-02-20T13:59:53.555Z",
      
    }
  ]
}

The amount sold is on cartItem.qty
It should be able to be filtered by order.createdAt as Date Range

The expected result should show Coca-Cola first followed by Fanta

I have also created a Mongo playgound via this link:
https://mongoplayground.net/p/Ihsghql9CCf

Tags:

2

Answers


  1. 0

    You should try something like this:

    db.orders.aggregate({
  "$unwind": "$cartItems"
},
{
  "$group": {
    _id: "$cartItems.product._id",
    "total": {
      $sum: "$cartItems.qty"
    },
    "name": {
      "$first": "$cartItems.product.name"
    },
    
  },
  
},
{
  $project: {
    _id: 0
  }
},
{
  "$sort": {
    total: -1
  }
})

    I did not work with the date range filter because you did not give me dates in BSON Date Format. But you should get it work alone with my base code:

    https://mongoplayground.net/p/XelC5pLfPJ6

    Login or Signup to reply.
  2. 0

    The most canonical way to achieve your desired ranking behaviour would be using $setWindowFields. You can define a popularity score and perform $rank on it.

    db.orders.aggregate([
  {
    $match: {
      createdAt: {
        $gte: ISODate("2023-02-19"),
        $lt: ISODate("2023-02-24")
      }
    }
  },
  {
    $set: {
      popularity: {
        $sum: "$cartItems.qty"
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "popularity": -1
      },
      "output": {
        "popularityRank": {
          $rank: {}
        }
      }
    }
  },
  {
    $sort: {
      popularityRank: -1
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search