K_CH
(K Ch)
April 7, 2022, 10:40am
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:{}
}
}}]
NeNaD
(Nenad Milosavljevic)
April 7, 2022, 11:40am
2
Hi,
Can you add example documents in the question, and expected output for these example documents?
1 Like
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
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