M121 Final Exam. Final: Question 5

Only one answer choice produces the desired result (the same is original query).
var pipeline = [
{"$sort": {“city”: 1}},
{"$addFields": { “min”: {"$min": “$sunnydays”}}},
{"$addFields": { “mean”: {"$avg": “$sunnydays” }}},
{"$match": { “country”: “USA”, “min”: {"$gte": 200}, “mean”: {"$gte": 220}}}
]

The rest is not producing the same result as original query because of wrong sequence of $addField and $match ($match on the added field happened before field added) or fields Min or Mean are missing from result. However Selection of this choice flagged as incorrect. Could you please clarify?

The choice you provided is not the only one that provides the same answer as the original query.

I agree that matching min will not work until min is added. However you do not need to addField min to obtain the same result.

It is always best to match early as to eliminate all documents that will be eliminated anyway. This way it prevents doing calculations on eliminated documents.

I understood that the first choice in the list is selecting the same documents as an original query, and does it in the optimal way (utilizing indexes), but it is not including field min into final output, so it is not the same result as produced by original query!
But it is flagged as correct answer.

Hi @M_G,

If you see the correct query, it is not using “min” function but it is anyways calculating the value.

You can refer documentation for more details on optimising the Aggregation pipeline:

Please feel free to reach out if you still have any questions.

Kind Regards,
Sonali

I think that @M_G might be right and wrong at the same time. Consider

 MongoDB Enterprise atlas-tpbbbx-shard-0:PRIMARY> db.cities.findOne()
{
	"_id" : 10,
	"city" : "San Diego",
	"region" : "CA",
	"country" : "USA",
	"sunnydays" : [
		220,
		232,
		205,
		211,
		242,
		270
	]
}
MongoDB Enterprise atlas-tpbbbx-shard-0:PRIMARY> var pipeline = [
...     {"$addFields": { "min": {"$min": "$sunnydays"}}},
...     {"$addFields": { "mean": {"$avg": "$sunnydays" }}},
...     {"$sort": {"city": 1}},
...     {"$match": { "country": "USA", "min": {"$gte": 200}, "mean": {"$gte": 220}}}
... ]
MongoDB Enterprise atlas-tpbbbx-shard-0:PRIMARY> db.cities.aggregate(pipeline).pretty()
{
	"_id" : 10,
	"city" : "San Diego",
	"region" : "CA",
	"country" : "USA",
	"sunnydays" : [
		220,
		232,
		205,
		211,
		242,
		270
	],
	"min" : 205,
	"mean" : 230
}
MongoDB Enterprise atlas-tpbbbx-shard-0:PRIMARY> var pipeline = [
...     {"$match": { "country": "USA"}},
...     {"$addFields": { "mean": {"$avg": "$sunnydays"}}},
...     {"$match": { "mean": {"$gte": 220}, "sunnydays": {"$not": {"$lt": 200 }}}},
...     {"$sort": {"city": 1}}
... ]
MongoDB Enterprise atlas-tpbbbx-shard-0:PRIMARY> db.cities.aggregate(pipeline).pretty()
{
	"_id" : 10,
	"city" : "San Diego",
	"region" : "CA",
	"country" : "USA",
	"sunnydays" : [
		220,
		232,
		205,
		211,
		242,
		270
	],
	"mean" : 230
}

If we look at the result of the second pipeline. The field min is not present as it is in the result of the first pipeline. So technically the second pipeline does not produce the same result, so M_G is right. However, the problem statement was not to have the same exact document but to answer We would like to find the cities in the USA where the minimum number of sunny days is 200 and the average number of sunny days is at least 220. Lastly, we’d like to have the results sorted by the city’s name. The matching documents may or may not have a different shape than the initial one.

Thanks M_G for making me rethink about this.

1 Like