Q:

MongoDB - Get SUM of values INSIDE of the array

I have JSON document recorded to MongoDB with structure like so:
[{ "SessionKey": "172e3b6b-509e-4ef3-950c-0c1dc5c83bab",
   "Query": {"Date": "2020-03-04"},
   "Flights": [
           {"LegId":"13235",
            "PricingOptions": [
                    {"Agents": [1963108],
                     "Price": 61763.64 },
                    {"Agents": [4035868],
                     "Price": 62395.83 }]},
            {"LegId": "13236",
             "PricingOptions": [{
                        "Agents": [2915951],
                        "Price": 37188.0}]}
                         ...

The result I'm trying to get is "LegId":"sum_per_flight", in this case -> {'13235': (61763.64+62395.83), '13236': 37188.0} and then get flights with price < N
I've tried to run this pipeline for aggregation step (but it returns list of ALL prices - I don't know how to sum them up properly):
result = collection.aggregate([
    {'$match': {'Query.Date': '2020-03-01'}},
    {'$group': {'_id': {'Flight':'$Flights.LegId', 'Price':'$Flights.PricingOptions.Price'}}} ])

Also I've tried this pipeline, but it returns 0 for 'total_price_per_flight':
result = collection.aggregate({'$project': {
        'Flights.LegId':1,
        'total_price_per_flight': {'$sum': '$Flights.PricingOptions.Price'}
    }})

A:

You need to use $unwind to flatten Flights array to able iterate individually.
With $reduce operator, we iterate PricingOptions array and sum Price fields (accumulate prices).
The last step we return your documents into original structure. Before that, you may apply "get flights with price < N"
db.collection.aggregate([
  {
    "$match": {
      "Query.Date": "2020-03-04"
    }
  },
  {
    $unwind: "$Flights"
  },
  {
    $addFields: {
      "Flights.LegId": {
        $arrayToObject: [
          [
            {
              k: "$Flights.LegId",
              v: {
                $reduce: {
                  input: "$Flights.PricingOptions",
                  initialValue: 0,
                  in: {
                    $add: [
                      "$$value",
                      "$$this.Price"
                    ]
                  }
                }
              }
            }
          ]
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      SessionKey: {
        $first: "$SessionKey"
      },
      Query: {
        $first: "$Query"
      },
      Flights: {
        $push: "$Flights"
      }
    }
  }
])

MongoPlayground

