CODEWITHSUNDEEP
mongodbmongoose
Max
Max

Reputation: 477

Mongoose subquery

I have a collection that looks like below:

[
  {
    "orderNum": "100",
    "createdTime": ISODate("2020-12-01T21:00:00.000Z"),
    "amount": 100,
    "memo": "100memo",
    "list": [
      1
    ]
  },
  {
    "orderNum": "200",
    "createdTime": ISODate("2020-12-01T21:01:00.000Z"),
    "amount": 200,
    "memo": "200memo",
    "list": [
      1,
      2
    ]
  },
  {
    "orderNum": "300",
    "createdTime": ISODate("2020-12-01T21:02:00.000Z"),
    "amount": 300,
    "memo": "300memo"
  },
  {
    "orderNum": "400",
    "createdTime": ISODate("2020-12-01T21:03:00.000Z"),
    "amount": 400,
    "memo": "400memo"
  },
  
]

and I'm trying to get the total amount of orders that were created before order# 300 (so order#100 and #200, total amount is 300).

Does anyone know how to get it via Mongoose?

Upvotes: 1

Views: 283

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

You can use this one:

db.collection.aggregate([
   { $sort: { orderNum: 1 } }, // by default the order of documents in a collection is undetermined
   { $group: { _id: null, data: { $push: "$$ROOT" } } }, // put all documents into one document
   { $set: { data: { $slice: ["$data", { $indexOfArray: ["$data.orderNum", "300"] }] } } }, // cut desired elementes from array
   { $unwind: "$data" }, // transform back to documents
   { $replaceRoot: { newRoot: "$data" } },
   { $group: { _id: null, total_amount: { $sum: "$amount" } } } // make summary
])

Actually it is not needed to $unwind and $group, so the shortcut would be this:

db.collection.aggregate([
   { $sort: { orderNum: 1 } },
   { $group: { _id: null, data: { $push: "$$ROOT" } } },
   { $set: { data: { $slice: ["$data", { $indexOfArray: ["$data.orderNum", "300"] }] } } },
   { $project: { total_amount: { $sum: "$data.amount" } } }
])

But the answer from @turivishal is even better.

Update for additional field

{ 
   $set: { 
      data: { $slice: ["$data", { $indexOfArray: ["$data.orderNum", "300"] }] },
      memo: { $arrayElemAt: [ "$data.memo", { $indexOfArray: ["$data.orderNum", "300"] } ] }
   } 
}

or

   { $set: { data: { $slice: ["$data", { $indexOfArray: ["$data.orderNum", "300"] }] } } },
   { $set: { memo: { $last: { "$data.memo" } } },

Upvotes: 1

turivishal
turivishal

Reputation: 36114

  • $match orderNum less than 300
  • $group by null and get totalAmount using $sum of amount
YourSchemaModel.aggregate([
  { $match: { orderNum: { $lt: "300" } } },
  {
    $group: {
      _id: null,
      totalAmount: { $sum: "$amount" }
    }
  }
])

Playground

Upvotes: 1

Related Questions