Details
-
Question
-
Resolution: Duplicate
-
Major - P3
-
None
-
None
-
None
-
None
Description
Given 2 documents and index:
db.elsort.drop();
|
db.elsort.insert({_id: 1, b: [{c: 2, d: 3}, {c: 3, d: 1}]}); |
db.elsort.insert({_id: 2, b: [{c: 2, d: 2}, {c: 3, d: 2}]}); |
db.elsort.ensureIndex({'b.c': 1, 'b.d': 1}) |
Need to get descending sorted documents using multikey index on array elements:
db.elsort.find({b: {$elemMatch: {c: 2}}}).sort({'b.c': -1, 'b.d': -1}) |
|
|
{ "_id" : 2, "b" : [ { "c" : 2, "d" : 2 }, { "c" : 3, "d" : 2 } ] } |
{ "_id" : 1, "b" : [ { "c" : 2, "d" : 3 }, { "c" : 3, "d" : 1 } ] } |
Documents are sorted not as desired. Sorting is wrapped globally:
db.elsort.find({b: {$elemMatch: {c: 2}}}).sort({'b.c': -1, 'b.d': -1}).explain().queryPlanner.winningPlan |
|
|
{
|
"stage" : "SORT", |
"sortPattern" : { |
"b.c" : -1, |
"b.d" : -1 |
},
|
"inputStage" : { |
"stage" : "SORT_KEY_GENERATOR", |
"inputStage" : { |
"stage" : "FETCH", |
"filter" : { |
"b" : { |
"$elemMatch" : { |
"c" : { |
"$eq" : 2 |
}
|
}
|
}
|
},
|
"inputStage" : { |
"stage" : "IXSCAN", |
"keyPattern" : { |
"b.c" : 1, |
"b.d" : 1 |
},
|
"indexName" : "b.c_1_b.d_1", |
"isMultiKey" : true, |
"multiKeyPaths" : { |
"b.c" : [ |
"b" |
],
|
"b.d" : [ |
"b" |
]
|
},
|
"isUnique" : false, |
"isSparse" : false, |
"isPartial" : false, |
"indexVersion" : 2, |
"direction" : "forward", |
"indexBounds" : { |
"b.c" : [ |
"[2.0, 2.0]" |
],
|
"b.d" : [ |
"[MinKey, MaxKey]" |
]
|
}
|
}
|
}
|
}
|
}
|
The desired effect for ascending sort can be reached by specifying a hint:
db.elsort.find({b: {$elemMatch: {c: 2}}}).hint({'b.c': 1, 'b.d': 1}) |
|
|
{ "_id" : 2, "b" : [ { "c" : 2, "d" : 2 }, { "c" : 3, "d" : 2 } ] } |
{ "_id" : 1, "b" : [ { "c" : 2, "d" : 3 }, { "c" : 3, "d" : 1 } ] } |
|
|
db.elsort.find({b: {$elemMatch: {c: 2}}}).hint({'b.c': 1, 'b.d': 1}).explain().queryPlanner.winningPlan |
|
|
{
|
"stage" : "FETCH", |
"filter" : { |
"b" : { |
"$elemMatch" : { |
"c" : { |
"$eq" : 2 |
}
|
}
|
}
|
},
|
"inputStage" : { |
"stage" : "IXSCAN", |
"keyPattern" : { |
"b.c" : 1, |
"b.d" : 1 |
},
|
"indexName" : "b.c_1_b.d_1", |
"isMultiKey" : true, |
"multiKeyPaths" : { |
"b.c" : [ |
"b" |
],
|
"b.d" : [ |
"b" |
]
|
},
|
"isUnique" : false, |
"isSparse" : false, |
"isPartial" : false, |
"indexVersion" : 2, |
"direction" : "forward", |
"indexBounds" : { |
"b.c" : [ |
"[2.0, 2.0]" |
],
|
"b.d" : [ |
"[MinKey, MaxKey]" |
]
|
}
|
}
|
}
|
It seems there is no way to force "direction" : "backward" for achieving an effect of descending sort.
Is there a way to achieve the desired effect?
Attachments
Issue Links
- duplicates
-
SERVER-20551 sort on multikey index after $elemMatch projection
-
- Closed
-