MongoDB: Reduce array of objects into a single object by computing the average of each field

Question

I use the MongoDB aggregation API to aggregate some data daily. The result of this aggregation is of this format:

[
  {
    aggDate: '2019-05-23',
    results: [
      {
        foo: 0.58,
        bar: 0.42
      }, {
        foo: 0.32,
        bar: 0.98
      }
    ]
  }
]

The aggregation on the date is fine, but now I would like to aggregate the objects in the results array.

The result of this aggregation should be of the following format:

[
  {
    aggDate: '2019-05-23',
    result: {
      foo: 0.45 // avg of all the `foo`, here: (0.58 + 0.32) / 2
      bar: 0.7 // avg of all the `bar`, here: (0.42 + 0.98) / 2
    }
  }
]

My problem here is that the keys foo and bar can change/new fields can be added in results objects. To avoid recoding the query each time it occurs, I want to use some generic way to say to MongoDB

Take this array of objects and reduce it into a single object where each value is the average of the same field in all objects.

I know the $reduce operator exists in MongoDB but I can't figure out how to use it and I am not even sure if it can help me here.

Ashh · Accepted Answer · 2019-05-24 12:07:25Z

8

You do not need to use $reduce. Simply $sum can do the job.

db.collection.aggregate([
  { "$project": {
    "result": {
      "foo": { "$divide": [{ "$sum": "$results.foo" }, { "$size": "$results" }] },
      "bar": { "$divide": [{ "$sum": "$results.bar" }, { "$size": "$results" }] }
    }
  }}
])

Update-> according to dynamic keys inside results array

db.collection.aggregate([
  { "$project": {
    "aggDate": 1,
    "results": {
      "$reduce": {
        "input": {
          "$map": { "input": "$results", "in": { "$objectToArray": "$$this" }}
        },
        "initialValue": [],
        "in": { "$concatArrays": ["$$value", "$$this"] }
      }
    }
  }},
  { "$project": {
    "aggDate": 1,
    "result": {
      "$arrayToObject": {
        "$map": { "input": { "$setUnion": ["$results.k"] },
          "as": "m",
          "in": {
            "$let": {
              "vars": {
                "fil": {
                  "$filter": {
                    "input": "$results",
                    "as": "d",
                    "cond": { "$eq": ["$$d.k", "$$m"] }
                  }
                }
              },
              "in": {
                "k": "$$m",
                "v": { "$divide": [{ "$sum": "$$fil.v" }, { "$size": "$$fil" }] }
              }
            }
          }
        }
      }
    }
  }}
])

MongoPlayground

With more simplified version and with single $project stage

db.collection.aggregate([
  { "$project": {
    "aggDate": 1,
    "result": {
      "$let": {
        "vars": {
          "red": {
            "$reduce": {
              "input": {
                "$map": { "input": "$results", "in": { "$objectToArray": "$$this" }}
              },
              "initialValue": [],
              "in": { "$concatArrays": ["$$value", "$$this"] }
            }
          }
        },
        "in": {
          "$arrayToObject": {
            "$map": { "input": { "$setUnion": ["$$red.k"] },
              "as": "m",
              "in": {
                "$let": {
                  "vars": {
                    "fil": {
                      "$filter": {
                        "input": "$$red",
                        "as": "d",
                        "cond": { "$eq": ["$$d.k", "$$m"] }
                      }
                    }
                  },
                  "in": {
                    "k": "$$m",
                    "v": { "$divide": [{ "$sum": "$$fil.v" }, { "$size": "$$fil" }] }
                  }
                }
              }
            }
          }
        }
      }
    }
  }}
])

MongoPlayground

And both output as

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "aggDate": "2019-05-23",
    "result": {
      "bar": 0.7,
      "foho": 0.32,
      "foo": 0.58,
      "sdbar": 0.98
    }
  }
]

edited May 24, 2019 at 12:07

answered May 23, 2019 at 17:28

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

5 Comments

Cédric Rémond Over a year ago

The problem is I don't want to explicitly use foo and bar in the query.

Ashh Over a year ago

@Korbraan Updated my answer

Cédric Rémond Over a year ago

Thank you, your solution works. However, the query seems slower than with the @mickl solution.

Ashh Over a year ago

@Korbraan Hmmm... Not possible. It is a single stage query. Where did you test it?

Cédric Rémond Over a year ago

I have a local database with some test data (~3000 documents). I use Nodejs and Mongoose and I benchmarked the queries using the MongoDB database profiler. However I have no index for now, it may change the result.

mickl · Accepted Answer · 2019-05-23 17:26:47Z

You should run $unwind and aggregate the data using $group stages. You also need $arrayToObject and $objectToArray to work with dynamic keys. $reduce is not an option here since keys are unknwon

db.col.aggregate([
    {
        $project: {
            aggDate: 1,
            results: {
                $map: { input: "$results", in: { $objectToArray: "$$this" } }
            }
        }
    },
    {
        $unwind: "$results"
    },
    {
        $unwind: "$results"
    },
    {
        $group: {
            _id: { aggDate: "$aggDate", k: "$results.k" },
            sum: { $sum: "$results.v" },
            count: { $sum: 1 }
        }
    },
    {
        $project: {
            _id: 1,
            v: { $divide: [ "$sum", "$count" ] }
        }
    },
    {
        $group: {
            _id: "$_id.aggDate",
            results: { $push: { k: "$_id.k", v: "$v" } }
        }
    },
    {
        $project: {
            _id: 0,
            aggDate: "$_id",
            results: { $arrayToObject: "$results" }
        }
    }
])

Mongo Playground

Tom Slabbaert · Accepted Answer · 2019-05-23 17:43:52Z

0

EDIT: not the most elegant but:

    { 
        "$unwind" : "$result"
    }, 
    { 
        "$addFields" : {
            "value" : {
                "$objectToArray" : "$result"
            }
        }
    }, 
    { 
        "$unwind" : "$value"
    }, 
    { 
        "$group" : {
            "_id" : null, 
            "length" : {
                "$sum" : 1.0
            }, 
            "total" : {
                "$sum" : "$value.v"
            }
        }
    }, 
    { 
        "$addFields" : {
            "avg" : {
                "$divide" : [
                    "$total", 
                    "$length"
                ]
            }
        }
    }

edited May 23, 2019 at 17:43

answered May 23, 2019 at 17:22

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

2 Comments

mickl Over a year ago

Did you test that somehow ? I'm getting an exception when trying to run this solution. please take a look: mongoplayground.net/p/Yh6ylcasOFj

Tom Slabbaert Over a year ago

my bad, i thought result is an object not an array

Collectives™ on Stack Overflow

MongoDB: Reduce array of objects into a single object by computing the average of each field

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related