Sorting by 2nd compound index element

[recepaltun]

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?

[recepaltun]

it was like talking to myself but I think the answer is here:

Use Indexes to Sort Query Results — MongoDB Manual

Sort and Non-prefix Subset of an Index

An index can support sort operations on a non-prefix subset of the index key pattern. To do so, the query must include equality conditions on all the prefix keys that precede the sort keys.

For example, the collection data has the following index:

{ a: 1, b: 1, c: 1, d: 1 }

The following operations can use the index to get the sort order:

Example	Index Prefix
db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } )	{ a: 1 , b: 1, c: 1 }
db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } )	{ a: 1, b: 1, c: 1 }
db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } )	{ a: 1, b: 1 }

As the last operation shows, only the index fields preceding the sort subset must have the equality conditions in the query document; the other index fields may specify other conditions.

If the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index. For example, the following operations specify a sort document of { c: 1 }, but the query documents do not contain equality matches on the preceding index fields a and b:

db.data.find( { a: { $gt: 2 } } ).sort( { c: 1 } )
db.data.find( { c: 5 } ).sort( { c: 1 } )

These operations will not efficiently use the index { a: 1, b: 1, c: 1, d: 1 } and may not even use the index to retrieve the documents.