How to process the fetched data from mongoDB?

Question

I want my order_total to be calculated from results after joining two different collections

I'm building an Food Ordering App. I've seperate model for item:"ItemModel"

Here's my orderSchema which is being followed while ordering the items. I've referred to the ItemModel in case of Item_Id

My OrderSchema:

const cartOrderSchema = new mongoose.Schema({
  items: [
    {
      item_id: { type: mongoose.Schema.Types.ObjectId, ref: "itemModel" },
      quantity: { type: Number, default: 1 },
    },
  ],
  user_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "userModel",
  },
  date: {
    type: Date,
    default: new Date(),
  },
  order_total: {
    type: Number,
    default: 0,
  },
  delivery_address: {
    type: String,
    default: "GGn",
  },
  payment_type: {
    type:String,
    default:"Cash"
  },
});

I'm using this query:

   await req.user.populate({
      path: "mycartOrder",
      populate:{
        path:"items.item_id"
      }
    });
    res.status(200).send(req.user.mycartOrder);

& getting result as:

{
        "order_total": 0,
        "delivery_address": "GGn",
        "payment_type": "Cash",
        "_id": "6270d84d89e3be5f291ee923",
        "items": [
            {
                "item_id": {
                    "item_type": "Veg",
                    "_id": "62614934693ffd8d8f7979e9",
                    "name": "Cheese Burger",
                    "cuisine_category": "Starters",
                    "price": 67,
                    "description": "Aloo patty enclosed within Bun Tikki with melted cheese",
                    "store_id": "626148ca693ffd8d8f7979e2",
                    "__v": 0
                },
                "quantity": 10,
                "_id": "6270d84d89e3be5f291ee924"
            },
            {
                "item_id": {
                    "item_type": "Veg",
                    "_id": "62614977693ffd8d8f7979ec",
                    "name": "Paneer Lababdar",
                    "cuisine_category": "Indian",
                    "price": 249,
                    "description": "Panner vegetable cooked in very rich Gravy",
                    "store_id": "626148ca693ffd8d8f7979e2",
                    "__v": 0
                },
                "quantity": 3,
                "_id": "6270d84d89e3be5f291ee925"
            }
        ],
        "date": "2022-05-03T07:22:03.907Z",
    },

---

What I want?

I want that this order_total should be calculated automatically when the query is executed by multiplying the item's price with quantity.

Please HELP!!

Answer 1

If your collection is called cart (I'm not familiar with node.js or mongoose), you can use the $unwind operator and unwind your items. Then use the $addFields operator to add a new field overwriting your order_total, with the value to be the multiplication of the price and quantity. Then you'll have to group them back together, using the $sum operator on all the order_total fields, and then re-add all the previous fields..

db.cart.aggregate([
    {
        $unwind: "$items"
    },
    {
        $addFields: {
            "order_total": {
                $multiply: [ "$items.quantity", "$items.item_id.price" ]
            }
        }
    }, 
    {
        $group: {
            _id: "$_id",
            order_total: {$sum: "$order_total"},
            delivery_address: { $first: "$delivery_address" },
            payment_type: { $first: "$payment_type" },
            items: { $push: "$items" },
            date: {$first: "$date" }
        }
    }
])

You can skip the $addFields step and do the sum and multiply all in one step but I wanted to show both for clarity:

db.cart.aggregate([
    {
        $unwind: "$items"
    }, 
    {
        $group: {
            _id: "$_id",
            order_total: {$sum: {$multiply: [ "$items.quantity", "$items.item_id.price" ]}},
            delivery_address: { $first: "$delivery_address" },
            payment_type: { $first: "$payment_type" },
            items: { $push: "$items" },
            date: {$first: "$date" }
        }
    }
])

You might need a match stage if you only want one specific cart.