MongoDB Query with sum

Question

I have a simple document setup:

{
    VG: "East",
    Artikellist: {
        Artikel1: "Sprite",
        Amount1: 1,
        Artikel2: "Fanta",
        Amount2: 3
    }
}

actually i just want to query these document to get a list of selling articels in each VG, or maybe town, doesnt matter. In addition the Query should sum the Amount of each product and give it back to me.

I Know i'm thinking in a SQL language, but that's actually the case.

May idea was this on here:

db.collection.aggregate([{
    $group: {
        _id: {
            VG: "$VG",
            Artikel1: "$Artikellist.Artikel1",
            Artikel2: "$Artikellist.Artikel2",
            $sum: "$Artikellist.Amount1",
            $sum: "$Artikellist.Amount2"
        },
    }
}]);

The hardest point here that i have 5 different values for VG and and it could be maximum of 5 Artikel and regarding amounts in one list.

So Hopefully you can help me here. Sry for my bad english and for my badder Mongo Skills.

$Sum and the likes will only work on fields with same names. You should have objects like {"artikel":"name","amount":"20"} in an array or collection. — s.d
– s.d, Commented Jul 16, 2014 at 17:24

algor · Accepted Answer · 2014-07-16 17:15:55Z

1

If Artikel1 is always "Sprite" and Artikel2 - "Fanta", then you can try this one:

db.test.aggregate({$group : {  _id : {VG : "$VG", Artikel1 : "$Artikellist.Artikel1", Artikel2 : "$Artikellist.Artikel2"}, Amount1 : {$sum : "$Artikellist.Amount1"},Amount2 : {$sum : "$Artikellist.Amount2"}}});

If values of Artikel1 and Artikel2 can vary I suggest changing the structure of document say to:

{
VG: "East",
Artikellist: [
    { Artikel: "Sprite",
      Amount: 1},
    { Artikel: "Fanta",
    Amount: 3 }
]}

and then use the following approach:

db.test.aggregate({$unwind : "$Artikellist"}, {$group : {_id : {VG : "$VG", Artikel : "$Artikellist.Artikel"}, Amount : {$sum : "$Artikellist.Amount"}}})

answered Jul 16, 2014 at 17:15

algor

4142 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3845857 Over a year ago

Thanks for your help :) Works as expected :)

Collectives™ on Stack Overflow

MongoDB Query with sum

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related