Search code examples
mongodbaggregation-framework

MongoDB Aggregate with mergeObjects


I have a statistics collection that contains the statistics for every day, so every day I have a different document, currently the only valid statistic that I have is "accuracy".

Accuracy is a Record<string, number> an object { [string]: number } basically, the keys are dynamic so I don't know beforehand what keys are going to be, but for this instance, there are valid keys: low, medium, high.

db.getCollection('statistics').aggregate( [
    {
        $group: {
            _id: null,
            accuracy: { $push: "$accuracy" }
        }
    },
] )

Which results in:

{
    "_id" : null,
    "accuracy" : [ 
        {
            "low" : 4
        }, 
        {
            "low" : 6
        }
    ]
}

But I don't want them to be both in different indexes, I want them to be accumulated and added together as follows:

{
    "_id" : null,
    "accuracy" : {
        "low" : 10
    }
}

So that led me to find this:

db.getCollection('statistics').aggregate( [
    {
        $group: {
            _id: null,
            accuracy: { $mergeObjects: "$accuracy" }
        }
    },
] )

The $mergeObjects, but with that another issue arises, it just merges and overrides the values and the output is wrong:

{
    "_id" : null,
    "accuracy" : {
        "low" : 6
    }
}

If anyone has any idea how to increment that count that would be much appreciated, $mergeObjects plus some sort of accumulation $add would be ideal but I couldn't find any useful resource in the docs for my problem.


Solution

    1. $set - Convert the key-value pair from the accuracy field to an array of objects consisting of k and v fields.

    2. $unwind - Descontruct the accuracy array into multiple documents.

    3. $group - Group by accuracy.k and perform sum with accuracy.v.

    4. $group - Group all documents, construct an array of objects consisting of k and v fields, convert the array into a key-value pair, and add into the accuracy array.

    db.collection.aggregate([
      {
        $set: {
          accuracy: {
            $objectToArray: "$accuracy"
          }
        }
      },
      {
        $unwind: "$accuracy"
      },
      {
        $group: {
          _id: "$accuracy.k",
          value: {
            $sum: "$accuracy.v"
          }
        }
      },
      {
        $group: {
          _id: null,
          accuracy: {
            $push: {
              $arrayToObject: [
                [
                  {
                    k: "$_id",
                    v: "$value"
                  }
                ]
              ]
            }
          }
        }
      }
    ])
    

    Demo @ Mongo Playground