Using max with aggregation group: not working

Working with Data
#1

Hi,

I made an aggregation query to investigate sample_mflix data on Atlas.

I tried to find the average, minimum, and maximum of the ratings by genre.
Everything seems to work fine except $max giving me “”.

I don’t understand why $max gives me a blank even though there are numbers inside a group.

Below is the pipeline.

[{$unwind: {
path: ‘$genres’,
}}, {$project: {
genres:1,
score:’$imdb.rating’
}}, {$sort: {
genres: 1,
score: 1
}}, {$group: {
_id: ‘$genres’,
avg_rating: {
$avg: ‘$score’
},
max_rating:{
$max:’$score’
},
min_rating:{
$min:’$score’
},
count:{
$count:{}
}
}}]

#2

Hi,

Can you add example documents in the question, and expected output for these example documents?

1 Like
#3

Hi @K_CH ,

This is because the sample dataset has some documents with imdb.rating of "" and the $max surface it as top, which unfortunately is a max value compared to numbers :frowning:

I suggest the following pipeline to avoid it:

[{$unwind: {
 path: '$genres'
}}, {$project: {
 genres: 1,
 score: '$imdb.rating'
}}, {$sort: {
 genres: 1,
 score: 1
}}, {$group: {
 _id: '$genres',
 avg_rating: {
  $avg: '$score'
 },
 max_rating: {
  $max: {
   $cond: [
    {
     $eq: [
      '$score',
      ''
     ]
    },
    0,
    '$score'
   ]
  }
 },
 min_rating: {
  $min: '$score'
 },
 count: {
  $count: {}
 }
}}]

Here I condition each score that is “” will turn into 0:

$max: {
   $cond: [
    {
     $eq: [
      '$score',
      ''
     ]
    },
    0,
    '$score'
   ]
  }
 }

Thanks
Pavel

2 Likes