mongodb - mongoose - How to get aggregated total and split of total sum which is also sum of items in mongoDB - answerstu - answerstu.com answerstu

mongoose - How to get aggregated total and split of total sum which is also sum of items in mongoDB

I have mongo document similar as follows:

{"client" : "ashwini",
    "data" : [ 
        {
            .....

            "ac" : [ 
                {
                    "cord" : [ 
                        5.0, 
                        0.0
                    ],
                    "power" : 10.0,on:true
                }, 
                {
                    "cord" : [ 
                        52.0, 
                        0.0
                    ],
                    "power" : 22.0,on:true
                }, 
                {
                    "cord" : [ 
                        85.0, 
                        0.0
                    ],
                    "power" : 50.0,on:false
                }, 
                {
                    "cord" : [ 
                        5.0, 
                        50.0
                    ],
                    "power" : 30.0,on:true
                }
            ],
            "fan" : [ 
                {
                    "cord" : [ 
                        10.0, 
                        20.0
                    ],
                    "power" : 50.0,on:true
                }, 
                {
                    "cord" : [ 
                        60.0, 
                        20.0
                    ],
                    "power" : 10.0,on:true
                }, 
                {
                    "cord" : [ 
                        85.0, 
                        20.0
                    ],
                    "power" : 40.0,on:false
                }, 
                {
                    "cord" : [ 
                        10.0, 
                        70.0
                    ],
                    "power" : 10.0,on:true
                }
            ],
            "light" : [ 
                {
                    "cord" : [ 
                        30.0, 
                        0.0
                    ],
                    "power" : 34.0,on:true
                }, 
                {
                    "cord" : [ 
                        66.0, 
                        0.0
                    ],
                    "power" : 10.0,on:true
                }, 
                {
                    "cord" : [ 
                        82.0, 
                        90.0
                    ],
                    "power" : 69.0,on:true
                }, 
                {
                    "cord" : [ 
                        20.0, 
                        50.0
                    ],
                    "power" : 70.0,on:true
                }
            ],
            "name" : "Palm Meadows",
            "floor" : -1.0
        }, 
        ......
    ]
 }

I want the output of my query as follows:

 {
        agr:{
          lights:10,
          ac:10,
          fan:20
        },
        split:[
          {
            name:"Palm Meadows",
            power:40,
            location:"Mahadevpura",
            lights:10,
            ac:10,
            fan:20
          },
       ....
       ],
}

where first agr is sum of all power in light/ac/fan field for client "ashwini" which are on and split has individual sum of light/ac/fan uniqued by name and location

I gave my first trial as follows

db.getCollection('property').aggregate([
                    {$match:{"client":"ashwini"}},
                    {
                        $group:{
                            _id:"$_id",
                            values:{
                                $addToSet:{
                                    name:"$data.name",
                                    floor:"$data.floor",
                                    location:"$data.location",
                                    lights:{$sum:"$data.lights.power"},
                                    fan:{$sum:"$data.fan.power"},
                                    ac:{$sum:"$data.ac.power"}
                                    }
                            }
                        }
                    }
                ])

which gave output as follows

enter image description here

I'm not sure how to achieve my requirement

1 Answer

  1. Isaac- Reply

    2019-11-13

    You can use below aggregation in 3.4.

    First part is to calculate the aggregate sum values for each location.

    Second part is to aggregate sum values for all locations.

    For each ($map) data row, $filter the data document where on field is true followed by $suming the power value for each requested field (inner $let).

    Outer $let expression to calculate the power values for each element which is the sum of requested fields along with aggregate requested field values, name and location as a split field inside $project stage.

    Aggregate all the requested fields values across all locations as a aggr field inside $addFields stage which also retains the existing values.

    db.getCollection('property').aggregate([
      {"$match":{"client":"ashwini"}},
      {"$project":{
        "_id":0,
        "split":{
          "$map":{
            "input":"$data",
            "as":"d",
            "in":{
              "$let":{
                "vars":{
                  "ac":{
                    "$sum":{
                      "$let":{
                        "vars":{"acm":{"$filter":{"input":"$$d.ac","as":"ac","cond":"$$ac.on"}}},
                        "in":"$$acm.power"
                      }
                    }
                  },
                  "fan":{
                    "$sum":{
                      "$let":{
                        "vars":{"fanm":{"$filter":{"input":"$$d.fan","as":"fan","cond":"$$fan.on"}}},
                        "in":"$$fanm.power"
                      }
                    }
                  },
                  "light":{
                    "$sum":{
                      "$let":{
                        "vars":{"lightm":{"$filter":{"input":"$$d.light","as":"light","cond":"$$light.on"}}},
                        "in":"$$lightm.power"
                      }
                    }
                  }
                },
                "in":{
                  "name":"$$d.name",
                  "location":"$$d.location",
                  "power":{"$sum":["$$ac","$$fan","$$light"]},
                  "ac":"$$ac",
                  "fan":"$$fan",
                  "light":"$$light"
                }
              }
            }
          }
        }
      }},
      {"$addFields":{
        "aggr":{
          "ac":{"$sum":"$split.ac"},
          "fan":{"$sum":"$split.fan"},
          "light":{"$sum":"$split.light"},
          "power":{"$sum":"split.power"}
        }
      }}
    ])
    

    Use below query to output computed values for location and name.

    $unwind data array followed by $group on location and name.

    $reduce to traverse the grouped data to sum all the requested field values.

    $group by _id to push the data values in array for computing values across all locations.

    db.getCollection('property').aggregate([
      {"$unwind":"$data"},
      {"$group":{"_id":{"id":"$_id","name":"$data.name", "location":"$data.location"},"data":{"$push":"$data"}}},
      {"$project":{
        "values":{
          "$reduce":{
            "input":"$data",
            "initialValue":{
                  "power":0,
                  "ac":0,
                  "fan":0,
                  "light":0
                },
            "in":{
              "$let":{
                "vars":{
                  "ac":{
                    "$sum":{
                      "$let":{
                        "vars":{"acm":{"$filter":{"input":"$$this.ac","as":"ac","cond":"$$ac.on"}}},
                        "in":"$$acm.power"
                      }
                    }
                  },
                  "fan":{
                    "$sum":{
                      "$let":{
                        "vars":{"fanm":{"$filter":{"input":"$$this.fan","as":"fan","cond":"$$fan.on"}}},
                        "in":"$$fanm.power"
                      }
                    }
                  },
                  "light":{
                    "$sum":{
                      "$let":{
                        "vars":{"lightm":{"$filter":{"input":"$$this.light","as":"light","cond":"$$light.on"}}},
                        "in":"$$lightm.power"
                      }
                    }
                  }
                },
                "in":{
                  "power":{"$add":["$$value.power", {"$sum":["$$ac","$$fan","$$light"]}]},
                  "ac":{"$add":["$$value.ac", "$$ac"]},
                  "fan":{"$add":["$$value.fan", "$$fan"]},
                  "light":{"$add":["$$value.light", "$$light"]}
                }
              }
            }
          }
        }
      }},
      {"$group":{"_id":"$_id.id", "split":{"$push":{"name":"$_id.name","location":"$_id.location","values":"$values"}}}},
      {"$addFields":{
        "aggr":{
          "ac":{"$sum":"$split.values.ac"},
          "fan":{"$sum":"$split.values.fan"},
          "light":{"$sum":"$split.values.light"},
          "power":{"$sum":"split.values.power"}
        }
      }}
    ])
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>