Calculate average for object values in MongoDB

Question

I want to sort these records in descending order based on their 2021 average rating:

[
  {
    "id": 1,
    "slug": "shop 1",
    "stats": {
      "time_stats": {
        "2021": {
          "1": 9.04,
          "2": 8.17,
          "10": 8.46,
          "7": 9.35,
          "11": 8.53,
          "6": 8.35,
          "5": 8.63,
          "9": 9.13,
          "3": 8.9,
          "8": 7.64,
          "4": 8.65,
          "12": 7.45
        },
        
      }
    }
  },
  {
    "id": 2,
    "slug": "shop 2",
    "stats": {
      "time_stats": {
        "2021": {
          "1": 5,
          "10": 9.35,
          "12": 6,
          "11": 8,
          "2": 3,
          "3": 5,
          "9": 4
        },
        
      }
    }
  },
  {
    "id": 3,
    "slug": "shop 3",
    "stats": {
      "time_stats": {
        "2021": {
          "3": 9.57,
          "11": 7.83,
          "4": 8.94,
          "2": 9.1,
          "6": 8.5,
          "5": 9.43,
          "12": 7.93,
          "10": 9.28,
          "9": 9.84,
          "7": 9.38,
          "1": 8.91,
          "8": 9.75
        },
        
      }
    }
  }
]

Each attribute inside 2021 object represents a month. I don't have any idea where to start, Here's what I've tried so far

db.collection.aggregate([
  {
    $unwind: "$stats"
  },
  {
    $unwind: "$stats.time_stats.2021"
  },
  {
    $group: {
      _id: "$slug",
      yearAvg: {
        $avg: {
          $sum: [
            "$stats.time_stats.2021.1",
            "$stats.time_stats.2021.2",
            "$stats.time_stats.2021.3",
            "$stats.time_stats.2021.4",
            "$stats.time_stats.2021.5",
            "$stats.time_stats.2021.6",
            "$stats.time_stats.2021.7",
            "$stats.time_stats.2021.8",
            "$stats.time_stats.2021.9",
            "$stats.time_stats.2021.10",
            "$stats.time_stats.2021.11",
            "$stats.time_stats.2021.12",
            
          ]
        }
      }
    }
  }
])

but I'm not sure how to proceed with the implementation. Any help is much appreciated!

chridam · Accepted Answer · 2022-01-18 10:59:09Z

To begin with, you would need a way of converting the time_stats hash into an array which will help you to derive the average since MongoDB already has useful operators for this e.g. $avg. The $avg operator works on an array operand to return the average. In your case, you require an operation which converts the hashmap

{
    "1": 9.04,
    "2": 8.17,
    "10": 8.46,
    "7": 9.35,
    "11": 8.53,
    "6": 8.35,
    "5": 8.63,
    "9": 9.13,
    "3": 8.9,
    "8": 7.64,
    "4": 8.65,
    "12": 7.45
}

to an array of key value object using $objectToArray operator, lets call the new field time_stats_2001_average:

{
    time_stats_2001_average: [
        { "k" : "1", "v" : 9.04 }, 
        { "k" : "2", "v" : 8.17 }, 
        { "k" : "3", "v" : 8.9 }, 
        { "k" : "4", "v" : 8.65 }, 
        { "k" : "5", "v" : 8.63 }, 
        { "k" : "6", "v" : 8.35 }, 
        { "k" : "7", "v" : 9.35 }, 
        { "k" : "8", "v" : 7.64 }, 
        { "k" : "9", "v" : 9.13 }, 
        { "k" : "10", "v" : 8.46 }, 
        { "k" : "11", "v" : 8.53 }, 
        { "k" : "12", "v" : 7.45 }
    ]
}

and then calculate the average using the expresion:

{ time_stats_2001_average: { $avg: '$time_stats_2001_average.v' } }

Sort the resulting documents on the new field with the sort operator as:

{ $sort : { time_stats_2001_average : -1 } }

Your full aggregate pipeline would look like this (Mongo Playground):

db.collection.aggregate([
    { '$addFields': {
        'time_stats_2001_average': {
            '$avg': {
                '$map': {
                    'input': {
                        '$objectToArray': '$stats.time_stats.2021'
                    },
                    'in': '$$this.v'
                }
            }
        }
    } },
    { '$sort': { 'time_stats_2001_average': -1 }  }
])

with another alternative pipeline (Mongo Playground):

db.collection.aggregate([
    { '$addFields': {
        'time_stats_2001_average': {
            '$objectToArray': '$stats.time_stats.2021'
        }
    } },
    { '$addFields': {
        'time_stats_2001_average': {
            '$avg': '$time_stats_2001_average.v'
        }
    } },
    { '$sort' : { 'time_stats_2001_average' : -1 } }
])

Collectives™ on Stack Overflow

Calculate average for object values in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related