[SERVER-36338] Multikey sorting of nested array element, selected with $elemMatch Created: 28/Jul/18  Updated: 04/Sep/18  Resolved: 30/Jul/18

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Taras Assignee: Nick Brewer
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-20551 sort on multikey index after $elemMat... Closed
Participants:

 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?



 Comments   
Comment by Taras [ 30/Jul/18 ]

nick.brewer,

Thank you for your reply. I'll post this to both of resources you've suggested as I believe this is essential issue to allow the specific index-optimal state of query planner which seems to be impossible.

 

I also update it with intuitive syntax for this case, which is not working and still produces the sorting wrap:

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]"
          ]
        }
      }
    }
  }
}

Comment by Nick Brewer [ 30/Jul/18 ]

tarquas I believe this is a duplicate of SERVER-20551, which describes some of the expected behavior for this use case.

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group.

-Nick

Generated at Thu Feb 08 04:42:48 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.