I have orders schema in my mongodb database, and I want to get top selling products ranked from highest to lowest this is how a document looks like:

So, as it can be seen there is a shopping_cart which is an array of products that can be distinguished by product_name and they each have quantity ( price is not relevant ).I've tried forming this query:

 db.orders.aggregate([
{
    $project : {
        "_id" : 0,
        "shopping_cart.product_name" : 1,
        "shopping_cart.qty" : 1,
    }
  }
 ])

And as a result I get this :

{ shopping_cart: 
  [ 
   { product_name: 'Seven Days', qty: 2 },
   { product_name: 'Cockta 0.33l', qty: 3 },
   { product_name: 'Marbo Chips', qty: 3 },
   { product_name: 'Coca Cola 2l', qty: 1 } 
   ]}
{ shopping_cart: 
   [
    { product_name: 'Seven Days', qty: 2 },
    { product_name: 'Coca Cola 2l', qty: 1 } 
   ]}
{ shopping_cart: 
   [
    { product_name: 'Integral Cookie', qty: 4 },
    { product_name: 'Seven Days', qty: 2 },
    { product_name: 'Marbo Chips', qty: 1 },
    { product_name: 'Coca Cola 2l', qty: 4 } 
    ]}

I would like to get something like this as an output ( I am making up data here ):

  { top_selling_products :  [
     'Integral Cookie','Coca Cola 2l','Marbo Chips''Seven Days'
   ]}

CodePudding user response：

• $unwind unwind shopping_cart
• $group group by product_name and sum qty
• $sort sort by sum
• $group group all and push name into array

aggregate

db.collection.aggregate([
  {
    "$unwind": "$shopping_cart"
  },
  {
    "$group": {
      "_id": "$shopping_cart.product_name",
      "sum": {
        "$sum": "$shopping_cart.qty"
      }
    }
  },
  {
    "$sort": {
      sum: -1
    }
  },
  {
    "$group": {
      "_id": null,
      "top_selling_products ": {
        $push: "$_id"
      }
    }
  }
])

data

[
  {
    shopping_cart: [
      {
        product_name: "Seven Days",
        qty: 2
      },
      {
        product_name: "Cockta 0.33l",
        qty: 3
      },
      {
        product_name: "Marbo Chips",
        qty: 3
      },
      {
        product_name: "Coca Cola 2l",
        qty: 1
      }
    ]
  },
  {
    shopping_cart: [
      {
        product_name: "Seven Days",
        qty: 2
      },
      {
        product_name: "Coca Cola 2l",
        qty: 1
      }
    ]
  },
  {
    shopping_cart: [
      {
        product_name: "Integral Cookie",
        qty: 4
      },
      {
        product_name: "Seven Days",
        qty: 2
      },
      {
        product_name: "Marbo Chips",
        qty: 1
      },
      {
        product_name: "Coca Cola 2l",
        qty: 4
      }
    ]
  }
]

result

[
  {
    "_id": null,
    "top_selling_products ": [
      "Seven Days",
      "Coca Cola 2l",
      "Integral Cookie",
      "Marbo Chips",
      "Cockta 0.33l"
    ]
  }
]

mongoplayground