MongoDB: Sum of all items in referenced collection

Question

I've got some issues getting the balance of all my accounts. All accounts have references to different deposit items in deposits collection.

Accounts collection:

[{
    "_id": "56b1ce63315748b44f1174e1",
    "name": "Foo bar",
    "deposits": [
        {
            "$oid": "56b1ce78315748b44f1174e2"
        }
    ]
}]

Deposits collection:

{
    "_id": {
        "$oid": "56b1deb84f40bfa435e22f3f"
    },
    "account": {
            "$oid": "56b1dea34f40bfa435e22f3e"
    },
    "amount": 300,
    "date": {
        "$date": "2016-02-01T00:00:00.000Z"
    }
}

I've tried to aggregate the query but it always returns balance: 0. I guess I need to populate the item before the use of aggregate. But how do I do that?

Accounts.aggregate([
    { $unwind: "$deposits" },
    {
        $group: {
            _id: "$_id",
            name: { "$first": "$name" },
            balance: { $sum: "$deposits.amount" }
        }
    }])

Solution:

{
    $lookup:
    {
        from: 'deposits',
        localField: 'amount',
        foreignField: 'deposits',
        as: 'deposits'
    },
},
{ $unwind: "$deposits" },
{
    $group: {
        id: "$_id",
        name: { "$first": "$name" },
        balance: { $sum: "$deposits.amount" }
    }
}

Answer 1

You need to use the $lookup operator in order to join the two collections, your current approach doesn't work. The $lookup operator is only available in Mongo 3.2 and higher.

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}