-
Type:
Question
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
None
-
None
-
None
-
None
-
None
-
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
-
-
- Closed
-