index created:
db.customers.createIndex({ active: 1, birthdate: -1, name: 1 });
sample document:
{
"_id": {
"$oid": "5ca4bbcea2dd94ee58162a88"
},
"username": "paul82",
"name": "Joseph Dawson",
"birthdate": {
"$date": {
"$numberLong": "-60209941000"
}
},
"email": "cgonzalez@yahoo.com",
"accounts": [ 158557 ],
"active": true
}
query:
db.customers.find({ birthdate: { $gt: ISODate('1995-08-01') }, active: true }).sort({ birthdate: -1, name: 1 });
explain output:
winningPlan: {
stage: 'FETCH',
inputStage: {
stage: 'IXSCAN',
keyPattern: {
active: 1,
birthdate: -1,
name: 1
},
indexName: 'active_1_birthdate_-1_name_1',
isMultiKey: false,
multiKeyPaths: {
active: [],
birthdate: [],
name: []
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: {
active: [
'[true, true]'
],
birthdate: [
'[new Date(9223372036854775807), new Date(807235200000))'
],
name: [
'[MinKey, MaxKey]'
]
}
}
},
rejectedPlans: []
}
I didn’t understand why this gave the correct result. I couldn’t make sense of the fact that the birth dates were sorted in the output of the query. Aren’t the birth dates supposed to be sorted within the active prefix?
So, how did it manage to provide the birth dates in a sorted manner without using an additional SORT for the ‘birthdate’ field?
Is there a place I missed? Can’t i expect the outputs to be sorted by the “active” field?