'MongoDB query to find top store from list of orders

I'm pretty new to Mongo. I have two collections that look as follows.

Order collection

[
    {
      id: 1,
      price: 249,
      store: 1,
      status: true
    },
    {
      id: 2,
      price: 230,
      store: 1,
      status: true
    },
    {
      id: 3,
      price: 240,
      store: 1,
      status: true
    },
    {
      id: 4,
      price: 100,
      store: 2,
      status: true
    },
    {
      id: 5,
      price: 150,
      store: 2,
      status: true
    },
    {
      id: 6,
      price: 500,
      store: 3,
      status: true
    },
    {
      id: 7,
      price: 70,
      store: 4,
      status: true
    },
]

Store Collection

[
    {
      id: 1,
      name: "Store A",
      status: true
    },
    {
      id: 2,
      name: "Store B",
      status: true
    },
    {
      id: 3,
      name: "Store C",
      status: true
    },
    {
      id: 4,
      name: "Store D",
      status: false
    }
]

How to find the top store from the list of orders, which should be based on the total sales in each store.

I have tried the following

db.order.aggregate([
  {
    "$match": {
      status: true
    }
  },
  {
    "$group": {
      "_id": "$store",
      "totalSale": {
        "$sum": "$price"
      }
    }
  },
  {
    $sort: {
      totoalSale: -1
    }
  }
])

I got the sorted list of stores from the above snippets. But I want to add store details along with total sales.

For more: https://mongoplayground.net/p/V3UH1r6YRnS

Expected Output

[
  {
    id: 1,
    name: "Store A",
    status: true,
    totalSale: 719
  },
  {
    id: 1,
    name: "Store c",
    status: true,
    totalSale: 500
  },
  {
    _id: 2,
    id: 1,
    name: "Store B",
    status: true,
    totalSale: 250
  },
  {
    _id: 4,
    name: "Store D",
    status: true,
    totalSale: 70
  }
]
mongodbmongoosemongodb-queryaggregation-framework


Solution 1:[1]

  1. $lookup - store collection joins order collection and generate new field store_orders.
  2. $set - Filter order with status: true from store_orders.
  3. $set - totalSale field sum for store_orders.price.
  4. $sort - Sort totalSale by descending.
  5. $unset - Remove store_orders field.
db.store.aggregate([
  {
    $lookup: {
      from: "order",
      localField: "id",
      foreignField: "store",
      as: "store_orders"
    }
  },
  {
    $set: {
      "store_orders": {
        $filter: {
          input: "$store_orders",
          as: "order",
          cond: {
            $eq: [
              "$$order.status",
              true
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      "totalSale": {
        "$sum": "$store_orders.price"
      }
    }
  },
  {
    $sort: {
      totalSale: -1
    }
  },
  {
    $unset: "store_orders"
  }
])

Sample Mongo Playground

Solution 2:[2]

You can start from store collection, $lookup the order collection, $sum the totalSales, then wrangle to your expected form

db.store.aggregate([
  {
    "$lookup": {
      "from": "order",
      let: {
        id: "$id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$id",
                "$store"
              ]
            }
          }
        },
        {
          $group: {
            _id: null,
            totalSale: {
              $sum: "$price"
            }
          }
        }
      ],
      "as": "totalSale"
    }
  },
  {
    $unwind: "$totalSale"
  },
  {
    $addFields: {
      totalSale: "$totalSale.totalSale"
    }
  },
  {
    $sort: {
      totalSale: -1
    }
  }
])

Here is the Mongo playground for youre reference.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Yong Shun
Solution 2

Related Questions