Add sum of order from order collection to user collection in mongoose

Question

I am currently working on a nodejs project and I am using mongodb as my database but I have an issue. I want to make a single call using aggregation that will add the sum of a specific user order to the user collection. But I am not sure how to go about it. Please I need some suggestions/help on how to achieve this. Please see the example of objects below.

User Collection

[
  {
    _id: 1,
    name: "Henry"
  },
  {
    _id: 2,
    name: "John"
  }
]

Order Collection

[
  {
    _id: 1,
    userId: 2
  },
  {
    _id: 2,
    userId: 2
  },
  {
    _id: 3,
    userId: 1
  }
]

Expected result after making the mongoose call

[
  {
    _id: 1,
    name: "Henry",
    orderCount: 1
  },
  {
    _id: 2,
    name: "John",
    orderCount: 2
  }
]

Does this answer your question? [mongoose sum a value across all documents](https://stackoverflow.com/questions/39588588/mongoose-sum-a-value-across-all-documents) — BroscR, Oct 16 '21 at 14:07

Takis · Answer 1 · 2022-04-09T17:35:55.503

Query

group orders by userId and sum the orderCount
lookup with users to get the user information(here the name field)

PlayMongo

orders.aggregate(
[{"$group": {"_id": "$userId", "orderCount": {"$sum": 1}}},
  {"$lookup": 
    {"from": "users",
     "localField": "_id",
     "foreignField": "_id",
     "as": "users"}},
  {"$set": {"name": {"$arrayElemAt": ["$users.name", 0]}}},
  {"$unset": ["users"]}])

If your users have more fields than just name replace the $set with this

{"$replaceRoot": 
  {"newRoot": {"$mergeObjects": ["$$ROOT" , {"$arrayElemAt": ["$users", 0]}]}}}

Add sum of order from order collection to user collection in mongoose

1 Answers1