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!
You can use this aggregation query:
$lookup to join collections and get values in a field called transactions.$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
collection.CountDocuments()
collection is the name of your collection. For instance if your collection name is example - example.CountDocuments()