I have a collection called orders

{
 "order_id":"123",
 "user_id":"u1",
 "history": [ "sh1","sh2" ]
}
{
 "order_id":"111",
 "user_id":"u1",
 "history": [ "sh3" ]
}

StatusHistory

{
  id: "sh1",
  event: "order_placed",
  amount: 100,
  timestamp: ".."
},
{
  id: "sh2",
  event: "do_refund",
  amount: 50,
  timestamp: ".."
},
{
  id: "sh3",
  event: "placed",
  amount: 300,
  timestamp: ".."
},

I have set a for loop doing required task, but need to optimize it. For single user, for all orders I need to populate history for that order and do sum of amount (for placed ) and subtract amount do_refund.

This is my effort to do the same. I need help.

db.getCollection('orders').aggregate(
[
    {
        $match: {
            user_id: 'u1',
        }
    },
    {

        $project: {
            order_id: 1,
            history:1,
            user_id:1
        }
    },
    {
        $unwind: '$history'
    },
    {
        $lookup: {
            from: "status_history",
            localField: "history",
            foreignField: "_id",
            as: "status_history_object"
        }
    },
    {
        $match: {
            'status_history_object.event': 'placed'
        }
    }
    //        ,
    //        {
    //            $group:
    //                {
    //                    _id: null,
    //                    sum: { $sum: "$status_history_object.payload.prices.amount_paid" }
    //                }
    //        }
])   

CodePudding user response：

You can do it like this:

• $group - to calculate total placed and total refund.
• $subtract - to calculate total.

db.orders.aggregate([
  {
    $match: {
      "user_id": "u1"
    }
  },
  {
    $project: {
      order_id: 1,
      history: 1,
      user_id: 1
    }
  },
  {
    $lookup: {
      from: "status_history",
      localField: "history",
      foreignField: "_id",
      as: "status_history_object"
    }
  },
  {
    $unwind: "$status_history_object"
  },
  {
    $group: {
      "_id": null,
      "total_placed": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$status_history_object.event",
                "placed"
              ]
            },
            "then": "$status_history_object.amount",
            "else": 0
          }
        }
      },
      "total_refund": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$status_history_object.event",
                "do_refund"
              ]
            },
            "then": "$status_history_object.amount",
            "else": 0
          }
        }
      }
    }
  },
  {
    $set: {
      total: {
        $subtract: [
          "$total_placed",
          "$total_refund"
        ]
      }
    }
  }
])

Working example