Lab 3.1: Incorrect answer

I don’t think the answer for the Lab 3.1 is correct: if the index { “address.state”: 1, “stars”: 1, “name”: 1 } was used, then there would be no SORT stage, since the query is filtered by address and stars, and sorted by name. Could you please explain the answer?

2 Likes

@ beatrizia

It seems fairly clear that you may need a sort here. The ‘name’ field is sorted within each value of ‘stars’, so if there are multiple names that have different star values, the sort will be required to put those into the correct order. As an illustration, suppose the documents include the following, with the query as shown:

{
“_id” : ObjectId("…"),
“name” : “New French”,
“cuisine” : “French”,
“stars” : 3,
“address” : {
“street” : “123 Santa Monica Square”,
“city” : “Santa Monica”,
“state” : “CA”,
“zipcode” : “90405”
}
}

{
“_id” : ObjectId("…"),
“name” : “Sample French”,
“cuisine” : “French”,
“stars” : 3,
“address” : {
“street” : “123 Santa Monica Square”,
“city” : “Santa Monica”,
“state” : “CA”,
“zipcode” : “90405”
}
}

{
“_id” : ObjectId("…"),
“name” : “Sample French”,
“cuisine” : “French”,
“stars” : 4,
“address” : {
“street” : “123 Santa Monica Square”,
“city” : “Santa Monica”,
“state” : “CA”,
“zipcode” : “90405”
}
}

{
“_id” : ObjectId("…"),
“name” : “David’s French”,
“cuisine” : “French”,
“stars” : 4,
“address” : {
“street” : “123 Santa Monica Square”,
“city” : “Santa Monica”,
“state” : “CA”,
“zipcode” : “90405”
}
}

db.restaurants.find({ “address.state”: “CA”, stars: { $gte: 3, $lte: 4 } }).sort({ name: 1 })

The result that you want is (somewhat abbreviated :wink: )

‘name’: “David’s French”
‘name’: “New French”
‘name’: “Sample French”
‘name’: “Sample French”

but clearly that’s not how the index is sorted, is it? So the query can’t rely on the index sort here; it has to make a internal sort to ensure that the names are correct across all values of 'stars.

6 Likes

Just fyi if the query is actually db.restaurants.find({ “address.state”: “CA”, stars: 4}).sort({ name: 1 }) Then we don’t need to sort. The fact that a range query is involved in the stars portion cause the name to be “a group of sorted” but not sorted by itself.

1 Like

// I chose to try them out on the dataset - the DB did behave consistently with the expected answer.

exp.find({ “address.state”: “NY”, stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED);
2019-03-26T09:06:52.632+0530 E QUERY [thread1] ReferenceError: REDACTED is not defined :

m201 lab 3.1 error
connecting to mongod on 27017

@Memosha_53183

Notice that this thread is more than 6 months old. If you have a new question, please post that in a new thread. Thanks.

Your explanation solved my puzzle exactly. Thank you so much.

1 Like