MongoDB aggregation: modify elements with a specific value in sub-sub-array-CodePudding

I have a MongoDB database having this kind of document structure:

{
  "CustomerID": 2019,  
  "Segment": "SME",  
  "Currency": "EUR",  
  "Transactions": [    
    {      "Date": 201302,      "Consumption": 26.34 },    
    {      "Date": 201303,      "Consumption": 3.3 },    
    {      "Date": 201304,      "Consumption": 3572.21 },    
 ]}

I'm trying to find a way to compute the total consumption of the customers in a specific time interval using an aggregation pipeline. My idea was setting the "consumption" field of the elements that don't match my condition to 0, and then sum all the different consumptions together (i.e. I want to find the total consumption between "201302" and "201303", if the date is not between [201302, 201303], set consumption to 0, then sum all the different consumptions).

The problem is that I'm having a lot of trouble to access these elements in the subarray and I really don't know how to tackle this problem, I'm really new to Mongo

Thank you so much in advance!

CodePudding user response：

Query

reduce starting from 0
if the Date is in that range add to $$value
else keep the old $$value
if you want this for all users also, like total of all customers you can add one more stage also

{"$group": 
  {"_id": null, 
   "globalTotalConsumption": {"$sum": "$totalConsumption"}}}

Playmongo

aggregate(
[{"$set": 
   {"totalConsumption": 
     {"$reduce": 
       {"input": "$Transactions",
        "initialValue": 0,
        "in": 
         {"$cond": 
           [{"$and": 
               [{"$gte": ["$$this.Date", 201302]},
                 {"$lte": ["$$this.Date", 201303]}]},
             {"$add": ["$$value", "$$this.Consumption"]}, "$$value"]}}}}}])