1

I am new to Mongodb, my version is 5.0.15. I was having doubt on the projected fields in the find query. I have the following query

db.movies.find({}, {title:1, rating:"$imdb.rating"}).sort({rating:1})

The above query doesn't sort using the rating field. If I replace rating with imdb.rating then it works fine. Can someone explain why is it so?

Like even if I use fields which are not projected but in the original document, the command sorts using them as well. I know I can achieve the same using aggregate but I want to know the reason why this not works. Any link to documentation would be appreciated.

Output -

Using db.movies.find({}, {title:1, rating: "$imdb.rating"}).sort({"rating":1}) enter image description here

Using db.movies.find({}, {title:1, rating: "$imdb.rating"}).sort({"imdb.rating":1}) enter image description here

Dhruv
  • 117
  • 11

1 Answers1

1

The docs on sort mention that when Interaction with Projection

When a set of results are both sorted and projected, the MongoDB query engine will always apply the sorting first.

When a MongoDB query is sorted and projected, the sorting is always applied first, before the projection. This means that the sort order is based on the original field, before it's projected.
So in your original document there is no field called rating. That is why you need to pass the field imdb.rating in your sort. Since rating is not existing in original document it will be considered as null in the sort (see this)

cmgchess
  • 7,996
  • 37
  • 44
  • 62
  • Ok, so except aggregation all other queries that have both projection and sorting will apply sorting first? – Dhruv Apr 04 '23 at 11:25
  • @Dhruv you are correct. also in aggregation you can put sort to the top of the pipeline to sort first – cmgchess Apr 04 '23 at 11:29