mongodb average arrays across many documents

user_3417413 0
Viewed 457

Using mongodb, I have a collection of documents where each document has a fixed length vector of floating point values such as below:

    items = [
        {"id": "1", "vec": [1, 2, 0]},
        {"id": "2", "vec": [6, 4, 1]},
        {"id": "3", "vec": [3, 2, 2]},
    ]

I would like to take the row wise average of these vectors. In this example I would expect the result to return

[ (1 + 6 + 3) / 3, (2 + 4 + 2) / 3, (0 + 1 + 2) / 3 ]

This answer is very close to what I am looking for, but as far as I can tell it will only work on vectors of size 2. mongoDB - average on array values

An answer has been provided that is not very performant for large arrays. For context I am using ~700 dimension vectors.

aws-documentdb mongodb
2 Answers

This should work: https://mongoplayground.net/p/PKXqmmW31nW

[
  {
    $group: {
      _id: null,
      a: {
        $push: {
          $arrayElemAt: ["$vec", 0]
        }
      },
      b: {
        $push: {
          $arrayElemAt: ["$vec", 1]
        }
      },
      c: {
        $push: {
          $arrayElemAt: ["$vec", 2]
        }
      }
    }
  },
  {
    $project: {
      a: {
        $avg: "$a"
      },
      b: {
        $avg: "$b"
      },
      c: {
        $avg: "$c"
      }
    }
  }
]

Which outputs:

[
  {
    "_id": null,
    "a": 3.3333333333333335,
    "b": 2.6666666666666665,
    "c": 1
  }
]

Here's a more efficient without $avg operator. I'll leave other answer up for reference. https://mongoplayground.net/p/rVERc8YjKZv

db.collection.aggregate([
  {
    $group: {
      _id: null,
      a: {
        $sum: {
          $arrayElemAt: ["$vec", 0]
        }
      },
      b: {
        $sum: {
          $arrayElemAt: ["$vec", 1]
        }
      },
      c: {
        $sum: {
          $arrayElemAt: ["$vec", 2]
        }
      },
      totalDocuments: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      a: {
        $divide: ["$a", "$totalDocuments"]
      },
      b: {
        $divide: ["$b", "$totalDocuments"]
      },
      c: {
        $divide: ["$c", "$totalDocuments"]
      }
    }
  }
])
user_138856150

You can use $unwind to get values into separate documents, the key is to keep the index of the values. Then you can use $group by the index and calculate the average using the $avg operator.

db.collection.aggregate([
  {
    $unwind: {
      path: "$vec",
      includeArrayIndex: "i" // unwind and keep index
    }
  },
  {
    $group: {
      _id: "$i", // group by index
      avg: { $avg: "$vec" }
    }
  }, // at this stage, you already get all the values you need, in separate documents. The following stages will put all the values in an array
  {
    $sort: { _id: 1 }
  },
  {
    $group: {
      _id: null,
      avg: { $push: "$avg" }
    }
  }
])

Mongo Playground

user_16567490
Related