I have two collections. One collection stores user data and another collection stores transaction data. The transaction data references user data using a foreign field on the user id.

How would I go about getting all user data with one additional field which is the sum of all linked transaction on the amount field?

The user model consist of id and various other fields e.g.

{
  id: unique_mongo_id
  name: Tom
  ...
}

The transaction table consists of two main fields which is a reference to the user Id and the value of the transaction e.g.

{
  userId: unique_mongo_user_id
  amount: 100
  ...
}

The output I am trying to achieve is a simple get of all user fields with one additional field which is the sum of all transactional data.

Many thanks for your help on this!

CodePudding user response：

You can use this aggregation query:

• First a $lookup to join collections and get values in a field called transactions.
• Then use $project to get the $sum (i.e. the total) of the field amount in the result joined.

db.user.aggregate([
  {
    "$lookup": {
      "from": "transactions",
      "localField": "_id",
      "foreignField": "userId",
      "as": "transactions"
    }
  },
  {
    "$project": {
      "name": 1,
      "total": {
        "$sum": "$transactions.amount"
      }
    }
  }
])

Example here

CodePudding user response：

collection.CountDocuments()

collection is the name of your collection. For instance if your collection name is example - example.CountDocuments()