mongodb adding up values in array based on some condition

Question

I can't get my head around a mongodb aggregation framework construction that adds up some values for each "_id" field documents... IF those values exist for the field "Wert".

E.g I have a document with _id field and a conditional ProduktTeilsummeDemonstrator":[] or "ProduktTeilsummeDemonstrator":[{Wert:342},{Wert:142}] that array can be empty or not, if it is empty, I want to add a new field "ProduktTeilsumme":0, else, I want to add up all values in that array to the new field...

The data that I have looks like this:

[{"_id":230,"ProduktSummeDemonstrator":713,"ProduktTeilsummeDemonstrator":[],"ProduktTeilsumme":null},{"_id":855,"ProduktSummeDemonstrator":1744,"ProduktTeilsummeDemonstrator":[],"ProduktTeilsumme":null},{"_id":767,"ProduktSummeDemonstrator":1010,"ProduktTeilsummeDemonstrator":[{"Zeitstempel":"2018-07-09T15:07:32.472Z","Wert":24},{"Zeitstempel":"2018-07-09T15:07:32.472Z","Wert":102},{"Zeitstempel":"2018-07-09T14:52:32.473Z","Wert":15},{"Zeitstempel":"2018-07-09T14:52:32.472Z","Wert":20},{"Zeitstempel":"2018-07-09T15:07:32.472Z","Wert":90},{"Zeitstempel":"2018-07-09T14:52:32.472Z","Wert":104},{"Zeitstempel":"2018-07-09T15:07:32.473Z","Wert":29},{"Zeitstempel":"2018-07-09T14:52:32.472Z","Wert":94},{"Zeitstempel":"2018-07-09T14:52:32.473Z","Wert":33},{"Zeitstempel":"2018-07-09T15:07:32.473Z","Wert":245},{"Zeitstempel":"2018-07-09T14:52:32.473Z","Wert":243},{"Zeitstempel":"2018-07-09T15:07:32.473Z","Wert":11}],"ProduktTeilsumme":null},{"_id":9,"ProduktSummeDemonstrator":94,"ProduktTeilsummeDemonstrator":[],"ProduktTeilsumme":null}]

I tried out different things with $reduce or $cond expressions, but somehow it won't add up: (Previously before that calculation stage I am grouping by ID and also filtering based on some time field condition..)

{
                $project: {
                    ProduktSummeDemonstrator: "$ProduktSummeDemonstrator",
                    ProduktTeilsummeDemonstrator: {
                        $filter: {
                            input: "$res",
                            as: "res",
                            cond: { $and: [
                                { $gte: ["$$res.Zeitstempel", new Date(req.params.start) ] },
                                { $lte: ["$$res.Zeitstempel", new Date(req.params.end) ] }
                            ] }
                        }
                    },
                    ProduktTeilsumme:  {/*
                        $reduce: {
                            input: "$ProduktTeilsummeDemonstrator",
                            initialValue:0,
                            in: {
                                $add: ["$$value","$$this.Wert"]
                            }
                        } */
                        $cond: { 
                            if: { $eq: [ "", "$ProduktTeilsummeDemonstrator" ] },
                            then: 0,
                            else: {
                                $reduce: {
                                    input: "$ProduktTeilsummeDemonstrator",
                                    initialValue: 0,
                                    in: {
                                        $add: ["$$value","$$this.Wert"]
                                    }

                                }
                            }
                        }                    
                    }
                }
            }

at least for "_id":767 I should get some values back, but I am getting "null" always.

s7vr · Accepted Answer · 2018-07-11 13:01:10Z

1

You have to use multiple project stages if you want to keep both the array and added value. One for $filtering ProduktTeilsummeDemonstrator followed by adding up array values.

Something like

[
  {"$project":{
    "ProduktSummeDemonstrator":1,
    "ProduktTeilsummeDemonstrator":{
      "$filter":{
        "input":"$ProduktTeilsummeDemonstrator",
        "as":"res",
        "cond":{
          "$and":[
            {"$gte":["$$res.Zeitstempel", new Date(req.params.start)]},
            {"$lte":["$$res.Zeitstempel", new Date(req.params.end)]}
          ]
        }
      }
    }
  }},
  {"$project":{
  "ProduktSummeDemonstrator":1,
  "ProduktTeilsummeDemonstrator":1,
  "ProduktTeilsumme":{"$sum":"$ProduktTeilsummeDemonstrator.Wert"}
  }}
]

answered Jul 11, 2018 at 13:01

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

Collectives™ on Stack Overflow

mongodb adding up values in array based on some condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related