-
Type: Question
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
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?
- duplicates
-
SERVER-20551 sort on multikey index after $elemMatch projection
- Closed