CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Erdem Aydemir
Erdem Aydemir

Reputation: 389

MongoDB - $sum in multiple collections

I have many collections. The view of these collections is the same as the JSON. What I want to do is to collect the collections according to their id and create a collection. How can I do it?

A.json

{
    "_id" : ObjectId("58455d2d506c1cab1c82152c"),
    "value" : 515835.0
}
{
    "_id" : ObjectId("58455d2d506c1cab1c82153c"),
    "value" : 6621696.0
}

B.json

{
    "_id" : ObjectId("58455d2d506c1cab1c82152c"),
    "value" : 2118.0
}
{
    "_id" : ObjectId("58455d2d506c1cab1c82153c"),
    "value" : 1190.0
}
{
    "_id" : ObjectId("423232d2d506c1cab1c1232c"),
    "value" : 10.0
}

Collect in A collection, id: 1, collection B id: 1 if it matches.

A in the collection, id: 2, if you are not in any collection, you are only showing that value.

In the last collection I have collected, I want to make a collection of objects and id in pairs.

Result.json

{
    "_id" : ObjectId("58455d2d506c1cab1c82152c"),
    "value" : 517953.0 // A.value + B.value
}
{
    "_id" : ObjectId("58455d2d506c1cab1c82153c"),
    "value" : 6633596.0 // A.value + B.value
}
{
    "_id" : ObjectId("423232d2d506c1cab1c1232c"),
    "value" : 10.0 // B.value (A.value : null)
}

i want this for multiple collections.

Upvotes: 0

Views: 1259

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

For 1-0/1 relation given in the example you can use $lookup as following:

db.B.aggregate([
    {$lookup: {
        from: "A",
        localField: "_id",
        foreignField: "_id",
        as: "a"
    }},
    {$unwind: {path: "$a", preserveNullAndEmptyArrays: true}},
    {$project: {
        value: {$add: ["$value", {$ifNull: ["$a.value", 0 ]}]}
    }}
]);

It does ignore any documents in A, which have no corresponding documents in B, i.e. result have the same number of documents as in collection B.

Upvotes: 1

Related Questions