CODEWITHSUNDEEP
mongodb
racumin
racumin

Reputation: 402

MongoDB get different sum for each list per document

I have these documents in my collection

{
    id:1,
    small:[{k:'A',v:1},{k:'B',v:2},{k:'D',v:3}],
    big:[{k:'A',v:2},{k:'B',v:3},{k:'C',v:1},{k:'D',v:4}]
},
{
    id:2,
    small:[{k:'A',v:1},{k:'B',v:2},{k:'D',v:3}],
    big:[{k:'A',v:2},{k:'B',v:3},{k:'C',v:1},{k:'D',v:4}]
},
{
    id:3,
    small:[{k:'A',v:1},{k:'B',v:2},{k:'D',v:3}],
    big:[{k:'A',v:2},{k:'B',v:3},{k:'C',v:1},{k:'D',v:4}]
}

Now, I want to get the sum for each key in both lists. I want my output to look like this:

{k:'A',small:3, big:6},
{k:'B',small:6, big:9},
{k:'D',small:9, big:12}

Notice that the output did not contain the key 'C'. This is because I only want to output the keys that are existing in the 'small' list. What mongodb functions should I use for this?

Thanks!

Upvotes: 0

Views: 22

Answers (1)

mickl
mickl

Reputation: 49975

Try below aggregation:

db.col.aggregate([
    { $unwind: "$small" },
    { $unwind: "$big" },
    { $redact: {
        $cond: {
           if: { $eq: [ "$small.k", "$big.k" ] },
           then: "$$KEEP",
           else: "$$PRUNE"
         }
       }
     },
     { 
        $group: { _id: "$small.k", small: { $sum: "$small.v" }, big: { $sum: "$big.v" } }
     },
     {
        $sort: { "_id": 1 }
     }
])

In general we need to have only one small and big in each document (that's why double $unwind). Then we want to keep only documents where keys are equal. That's the moment where C is filtered out - has no pair in small and we're utilizing $redact for that. Aggregation is just a $group with $sum.

Upvotes: 1

Related Questions